mysql - Database modeling for a weak entity

Question

I have 2 tables in my database orders and orderHistory.

 -----------------                    -----------------------
 |  orders       |                    |  orderHistory       |
 -----------------                    -----------------------
 | orderID  (PK) |                    | historyLineID  (PK) |
 | orderDate     |                    | status              |
 | price         |                    | quantity            |
 -----------------                    -----------------------

Now an order can have multiple history lines. However, a history line can't exist on its own. I heard this is called a weak entity and therefore the PK from orders must be part of the PK of table orderHistory.

Questions

1. Is this really a correct weak entity relationship? Is there other ways to identify them?
2. Should I add the PK of table order to table orderHistory and make it a composite primary key?
3. In case I decide to add a new record to orderHistory, how will I add a new composite key? (orderID is available from table orders, but historyLineID should be auto incremented.)
4. What if I decide to model this as a normal One-To-Many relationship where orderID is added as a foreign key only instead? what are the cons of doing so?
5. Will ignoring Weak entities at all cause any problems later in a design provided all tables are in 3rd normal form?

Note

Both orderID & historyLineID are surrogate keys. Thanks in advance.

Answer 1

An entity is not weak because it can't exist independently, but because it can't be identified independently. Therefore, a relationship that "leads" to a weak entity is called "identifying" relationship. In practice, this means that the parent's primary key is migrated into (usually proper) subset of child's PK (the term "weak entity" is usually defined in relation to primary keys, though it could in theory apply to any key).

It is perfectly legit to have an entity that can't exist independently, but can be identified independently - in other words, that is in a non-identifying relationship to a non-NULL.

You have to ask: can historyLineID be unique alone, or in combination with orderID? I suspect the latter is the case, which would make it a weak entity.

Is this really a correct weak entity relationship?

What you have shown us isn't a weak entity - parent's PK is not migrated into the child's PK.

Is there other ways to identify them?

You have essentially two options:

• orderHistory has a composite PK: {orderID, historyLineID}, where orderID is FK. BTW, this PK could be considered "natural":

  

• orderHistory has a surrogate PK: {orderHistoryID}, while orderID is outside of the PK. You'd still need to have an alternate key {orderID, historyLineID} though:

  

Should I add the PK of table order to table orderHistory and make it a composite primary key?

Yes, this is the first option described above. Unless you have child relationships on orderHistory itself, this is also the best solution. If orderHistory does have children, than this may or may not be the best solution, depending on several factors.

What if I decide to model this as a normal One-To-Many relationship where orderID is added as a foreign key instead? what are the cons of doing so?

This is not either-or. A field can be both FK and a part of a (primary or alternate) key, as shown above.

Will ignoring Weak entities at all cause any problems later in a design provided all tables are in 3rd normal form?

You won't be able to reach 3NF unless you specify your keys correctly, and you won't be able to do that without considering which entity can be identified independently and which can't.