Database modeling for a weak object

I have two tables in my database ordersand orderHistory.

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

Now ordermay have a few history lines. However, a history linecannot exist independently. I heard that this is called a weak entity, so PK from ordersshould be part of the PK table orderHistory.

Questions

  • Is this really the right loosely coupled relationship? Are there other ways to identify them?
  • Should I add PK tables orderto the table orderHistoryand make it a composite primary key?
  • If I decide to add a new entry to orderHistory, how do I add a new composite key? ( orderIDAvailable from the table orders, but historyLineIDshould automatically increase.)
  • What should I do if I decide to use this as the usual one-to-many relationship , where orderIDa foreign key is added instead? What are the disadvantages of this?
  • Will ignore Weak entities, in any case there will be problems in the design, if all the tables are in the 3rd normal form?

Note

Both orderIDand historyLineIDare surrogate keys. Thanks in advance.

+5
source share
2 answers

, , , . , "" , "" . , ( proper) PK ( " " , ).

, , - , -NULL.

: historyLineID orderID? , , .

?

, , - .

?

:

  • orderHistory PK: {orderID, historyLineID}, orderID - FK. , "":

    enter image description here

  • orderHistory PK: {orderHistoryID}, orderID - PK. {orderID, historyLineID}, :

    enter image description here

PK orderHistory ?

, , . orderHistory, . orderHistory , , .

, "--", OrderID ? ?

, . FK, ( ) , .

, 3- ?

3NF, , , , .

+7
  • - , , , . - , orderID, ?

- .   orderId "" History... 2. , orderID historyLineID 3. orderID. order.orderID, historyLineID, -, . 4. . , script, , , . orderID (parent) historyLineID ( ), , , , .

LINK:

0

All Articles