Relationship Design Question

Hi,

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:-

Products
Quality
Design
Design_Version
Design_Version_Details

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

Product_ID
Quality_ID
Design_ID
Design_Version_ID
Design_Version_Details_ID
Size

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.

Because AppSheet is not an RDBMS. strict normalization is often not reasonably possible. Your design for Products seems reasonable to me. You just have to remain aware of the data duplication and ensure consistency should the dependencies change.

2 Likes