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
- Is this really a correct weak entity relationship? Is there other ways to identify them?
- Should I add the PK of table
order
to tableorderHistory
and make it a composite primary key? - In case I decide to add a new record to
orderHistory
, how will I add a new composite key? (orderID
is available from tableorders
, buthistoryLineID
should be auto incremented.) - 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? - 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.