Which one is good database design when creating child tables?

Question

Hi all, There are two methods which I noticed while designing child tables

Method 1 Example:

Master Table >>Order Table (order_id pk)
Child Table >>order_products 
table (order_id,product_id, quantity,PK (order_id,product_id))

Method 2 example:

Master Table >>Order Table (order_id pk)
Child Table >>order_products table (order_product_id PK, order_id,product_id, quantity)

Question: Notice that We are using addition order_product_id in the second method. That is my question, is it goo to use combined primary key or introduce a new column like in method 2?

what are pros and cons. is the anwer depends on the relationship? (in case of one-to-many method 1 is better or in case of many-to-many method 2 is better etc)

Answer 1

You already seem to understand (or do you?) the difference between one-to-many and many-to-many relationships, so I am not sure what you are asking.

When you have one-to-many, use the second pattern; when you have many-to-many, you need the extra table of the first pattern.

Answer 2

Hi,

the second one's the choice to go since you have a composite primary key in the first one:

PK (order_id,product_id)

so you always need these two values to refer to a record.

I'd recommend the second one and if you need some restrictions create a unique index on order_id and product_id.

Answer 3

This is not a design question - it is about functionality.

In yourfirst example - how do you handle someone ordering 3 copies of a book? entering 3 different time the same book (2 childs)?

In general - sorry - the details should contain all reelvant information. THis includes amount, but also includes texts and price information.

Why?

Because depending on yourbusiness you may change the description of an item or the price AFTER the order has arrived, which may change it for FUTURE orders, but not for orders already in processing.

Imagine I go to your shop and order a Widget for 9,99, then - 2 minutes later - someone changes theprice to 14,99. I still am supposed to get the Widget for 9,99, as this was the vlaid price when my order was entered. If you would proces my order for 14,99, it would be fraud.

I stronyl suggest getting professional. Get a copy of the Data Model Ressoure Book, Volume 1. Has a lot of data models for standrd scnearios - like oder s handling.

Answer 4

I wouldn't think in many-to-many at all. This is a simple one-to-many relation. You should identify the OrderItem as an own entity, not a link table.

• Order has many OrderItems
• an OrderItem has a product, quantity etc.
• There might be a unique constraint for order and product. But is it necessary?

Looking at it this way, it is obviously solution two:

• Order (order_id pk)
• OrderItem (OrderItem_id PK, order_id (FK), product_id (FK), quantity)