Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a shopping cart table used to store customer id and product id of their chosen items. However I have multiple product tables, for example cars, books, houses. I started out with one table but then realized using an auto increment key for the product tables might cause a problem, as I will not know which table to join the shopping cart with. I originally started out with one table so I did not have this problem before, but now I have to expand it to accommodate two additional product tables. I am looking for some help in how to restructure this schema to properly store products to the cart table.

Cars

╔══════════╦═══════════╦══════════╗
║ CarID    ║ Model     ║ Brand    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ E 320     ║ Bmw      ║
║        1 ║ JR 505    ║ Audi     ║
╚══════════╩═══════════╩══════════╝

Books

╔══════════╦═══════════╦══════════╗
║ Book ID  ║ Author    ║ Title    ║
╠══════════╬═══════════╬══════════╣
║        0 ║ A.R       ║ Smithy   ║
║        1 ║ J.R.      ║ Hailey's ║
╚══════════╩═══════════╩══════════╝

Houses

╔══════════╦═══════════╦══════════╗
║ HouseID  ║ Address   ║ Town     ║
╠══════════╬═══════════╬══════════╣
║        0 ║ Belmont Av║ Bull Bay ║
║        1 ║ Cedar Drv ║ Cinclairs║
╚══════════╩═══════════╩══════════╝

Cart Table

╔══════════╦═══════════╦══════════╗
║ CartID   ║ memberID  ║ ProductID║
╠══════════╬═══════════╬══════════╣
║        0 ║ 3         ║ 1        ║
║        1 ║ 5         ║ 0        ║
╚══════════╩═══════════╩══════════╝
share|improve this question
    
You can solve this by having a unique ID for each table, then a type_id in your shopping cart table. eg: Cars are type_id 1, books are type_id 2 etc. –  Phil Dec 22 '13 at 0:35
    
Thanks for your speedy reply, could you perhaps show me a sample query involving a type_id? –  kabuto178 Dec 22 '13 at 0:37

1 Answer 1

I would solve this by adding a generic PRODUCT table containing the attributes which are common for all products (including the ProductID) and a ProductType column describing the type of product (Car, Book etc). Tables CAR, BOOK can then contain the same ProductID as in the main PRODUCT table and attributes which are specific to their product type.

share|improve this answer
1  
This is known as "entity-subtyping" and is the appropriate way to handle the OP's situation. The product table in such a scenario is often referred to as a SKU (Stock Keeping Unit). –  Joel Brown Dec 23 '13 at 12:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.