Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
458 views
in Technique[技术] by (71.8m points)

mysql - Database modeling for a weak entity

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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":

    enter image description here

  • 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:

    enter image description here

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...