I have an hesitation how to organise the following database for orthodontic products with the following aspects.
I try to be as more specific as possible.
Each product has a specific unique name but available with different characteristics: some are static related to the product itself while others are dynamic sells-based.
"Product" static characteristics (their values may change very rarely):
"Specific" dynamic characteristics (packages and prices will change over time):
- Pieces into package: 1, 5, 10, 30, 60, 90, 120
- Purchase Price: based on number of pieces for package
- Selling Price: based on number of pieces for package
- Availability of samples: Yes/No (in case of Yes, the samples might be: 1, 5,10)
So for example, product "Anchor" can be available in different packages, each with its own purchase price and selling price, and may, or may not be available as sample.
I have made:
- "Products_table" where are recorded the product characteristics
- "Specifics_table" where are stored different packages and prices for each product
The two above are linked together by Product_key (UUID).
- "Clients_table" with buyers informations
- "Sells_table" where is stored every time a product with a specific packages is sold (Product name is selected with a drop-down list)
The three above are linked together by Clients_key (UUID).
Before proceeding further, my hesitation: how to link "Products_table" and "Specifics_table" with "Sells_table" and "Invoice_table".
I guess I have two options:
A) someone suggested me to use Product_key:
easier, the drop-down list in "Sells_table" is shorter, but that means that in "Specifics" every record related to a Product has the same Product_Key
(I am not sure if this options is correct).
B) using a Specifics_key for each record in "Specifics_table":
a bit more complex, the drop-down list in "Sells_table" would be much longer since each product availability would have its own Key and name, for example the product "Anchor" should be named accordingly with package content "Anchor (5)", "Anchor (10)", "Anchor (30)", etc
(this would be my instinct choice but not sure it is required its complexity)
Many thanks for your suggestions,