CODEWITHSUNDEEP
entity-frameworkdatabase-design
zzzzz
zzzzz

Reputation: 75

Does a weak entity need a partial key?

Does a weak entity need a partial key? Or can you just use its parent key as its primary key.

i.e Order and OrderItem. Order has a PK OrderPK, whilst OrderItem has no partial key.

Is this considered bad practice?

Upvotes: 1

Views: 2519

Answers (1)

TommCatt
TommCatt

Reputation: 5636

The OrderItem table should have an OrderID field that makes a FK reference back to the Orders table. This assures each item is for a valid order.

Then there is usually another field with distinguishes each item which would be used together with the OrderID field to form the primary key for the item.

This could be an intrinsic value or values that is unique for each item within an order. SKU or PartNum might be just such a value, assuming that multiple occurrences of the same item would be merged into one entry. To find this value, just ask yourself what minimum amount of data would you need to uniquely identify one item from another within the same order. However, it may not be possible. A disadvantage of this scheme is that you could be using dynamic data for a key field. The SKU of a particular item could well change some time in the future.

Or there could be a sequential value (1, 2, 3,...) for each item in an order. A disadvantage with this scheme is the sequential values cannot be system generated. Each sequence is unique for each order and this must be generated by trigger or application code.

Or there could be a system-generated sequential value unique to all the items for all the orders and this field could be the lone primary key. Per-order sequential values could still be generated by row_number functions in queries, but this means a particular item could have different values in different queries. That may or may not be a problem.

At this point, only you know enough about your system to choose the best option. But it is generally necessary for users to be able to select one specific item of one specific order, so some sort of key definition for each item is usually necessary.

Upvotes: 1

Related Questions