0

I have the following tables: tblCustomer, tblProductA and tblProductB. I have also an order table tblOrder. Each order consists of a customer and one or more products from tblProductA and one or more products tblProductB.

The standard case is when we have only one table for products, and we usually use a join table to connect orders and products.

However, in the case I am bringing, it is mandatory to have two tables for products (tblProductA and tblProductB). How do I model this case?

1
  • 1
    What is the reason for "mandatory to have two tables for products"? You are essentially breaking 1st Normal Form and asking how to model it... well the answer is if you want to model it efficiently, you must use the same table!
    – blobbles
    Oct 28, 2016 at 2:45

1 Answer 1

1

There are 2 options:

  1. If your productType has same attribute: Multiple Product with same attribute

  2. If your ProductType has different attribute: Multiple Product with different attribute. The ProductID in ProductA and ProductB is have one-to-one relation to Product at coloumn ID

4
  • The diagrams are very helpful. You could have joined ProductA and ProductB with the generalization symbol in er diagramming, and then linked that to product, but your depiction is good enough. This would basically be implemented using two techniques, namely class table inheritance, and shared primary key. Oct 28, 2016 at 6:56
  • This isn't how things would usually be done. You'd ordinarily have a common products table that holds products all with common attributes, then use an EAV model to store attributes specific to each product
    – Philᵀᴹ
    Oct 28, 2016 at 7:31
  • Thanks @WalterMitty. FYI, my english is not good enough, so not too much explanation i can write. Oct 28, 2016 at 8:57
  • @Philᵀᴹ, so do i, when i get my current job. My boss introduce me to this design. Oct 28, 2016 at 8:58

Your Answer

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

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