I am using AppSheet to make a simple app for my carpet manufacturing work. I am confused about one aspect of relationship design so would appreciate it if someone could look over my table design
I have the following tables:-
Quality defines the construction of a carpet. One quality can have several designs, but any design has only 1 quality
Design table has the design name and code. Any design can have many design versions (color combinations)
Design_version is for storing the color combinations of a design. It simply links to the design id and notes the design version ‘A’, ‘B’, ‘C’…so on.
Design_Version_details note all the colors in a particular design version and their percentages. For example for a design 25, Version A, we have Color Red 20% and Color Black 80%. That goes in this table. This is a child of design_version
So far so good. I think I followed all the normalization rules.
My confusion comes in the Products table
Forgetting normalization for a moment, Ideally, I would like the Products table to have the following information
But then this seems to me that I am breaking normalization rules as Design_Version_Details is dependent on Design_version which is itself dependent on Design which is dependent on Quality. So should I only have Product_ID, Design_Version_Details_ID and Size in this table?
How should I make this table instead if this is not the correct way to go about it?
When I create a UX form for Products, I can select the Quality and the design but then the dropdown for design_version shows all the design versions and not just specific to the previously selected design which indicates to me that I am not doing it right.