Table Design For Multiple Different Products On One Order

Question

If I were to have an online shopping website that sold apples and monitors and these were stored in different tables because the distinguishing property of apples is colour and that of monitors is resolution how would I add these both to an invoice table whilst still retaining referential integrity and not unioning these tables?

                        Invoices(InvoiceId)
                                 |
                  InvoiceItems(ItemId, ProductId)
                                 |
                       Products(ProductId)
              |                                            |
Apples(AppleId, ProductId, Colour)   Monitors(MonitorId, ProductId, Resolution)

Answer 1

In the first place, I would store them in a single Products table, not in two different tables.

In the second place, (unless each invoice was for only one product) I would not add them to a single invoice table - instead, I would set up an Invoice_Products table, to link between the tables.

I suggest you look into Database Normalisation.

Answer 2

So Invoice table has a ProductID FK, and a ProductID can be either an AppleID (PK color) or MonitorID (PK resolution)?

If so, you can introduce a ProductTypeID with values like 0=apple, 1=monitor, or a isProductTypeApple boolean if there's only ever going to be 2 product types, and include that in the ProductID table PK.

You also need to include the ProductTypeID field in the Apple table and Monitor table PK.

Answer 3

I like name-value tables for these...It might be easier to redesign so it goes 'Product' and then 'product details'...product details holds the product id, the detail type and then the value. This would allow you to hold apples and monitors in the same table regardless of identifying attribute (and leave it open for other product to be added later on).

Similiar approach can be taken in the invoice table...have a 'product_type' column that tells you which table to look into (apple or monitor) and then a 'product_id' that references whatever ID column is in the apple/monitor table. Querying on a setup like this is a bit difficult and may force you to use dynamic sql...I'd only take this route if you have no control over doing the redesign above (and other answers posted here refer to)

First solution is preferential I would think...change the design on this db to the name value pair with the products and you'll save headaches writing your queries later.

Answer 4

A question for your data model is You need a reference scheme will you use to identify products? Maybe SKU ?

Then identify each apple as a product by assigning an SKU. Likewise for monitors. Then use the SKU in the invoice item. Something like this:

product {sku} key {sku};

invoice_item {invoice_id, sku} key {invoice_id, sku} ;

apple {color, sku} key {color} key {sku};

monitor {size, sku} key {size} key {sku};

with appropriate constrains... in particular, the union of apple {sku} and monitor {sku} == product {sku}.