0

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        ║
╚══════════╩═══════════╩══════════╝
Improve this question
2
  • 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, 2013 at 0:35
  • Thanks for your speedy reply, could you perhaps show me a sample query involving a type_id?
    – kabuto178
    Dec 22, 2013 at 0:37

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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, 2013 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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