|
|
Posted by Trevor Best on 10/12/05 03:08
Erland Sommarskog wrote:
> Having a one-column key in all tables is actually a really poor idea.
> Typical example: an order has a one-column key, which is an orderid.
> But a detail row on a order has a two column key: an orderid and a
> row number (or a product id).
A problem arises when you have more than a simple system. You have:
Project (ProjectNo)
Order (ProjectNo, OrderNo)
Items (ProjectNo, OrderNo, ItemNo)
Expediting Split components [1] (ProjectNo, OrderNo, ItemNo, CompSplit)
Expediting Split Deliveries (ProjectNo, OrderNo, ItemNo, CompSplit,
DelSplit)
In real world, expeditor has no idea that freight forwarder can't fit
1,000,000 bricks on one truck (don't laff, I've seen it happen) so MMTs
(Material Movement Ticket) are split (ProjectNo, OrderNo, ItemNo,
CompSplit, DelSplit, MMTNo)
The MMT may go onto a container, then later on is split into trucks
(that move at different speeds), or some items get held longer in
customs than others resulting in many MRRs (Material Received Report)
for one MMT, MMR Item has a key that looks like a complete table before
any meaningful data gets there.
Now, given that having 7 columns in a PK is stupid, do you:
a) Give a surrogate key to some tables and a natural key to others and
thereby be inconsistent or
b) Be consistent and use surrogate keys?
Given that no-one in their right mind would use just a surrogate key as
the unique constraint but also have a candidate key so no real
duplicates can arise. Also that these surrogate keys (Identity columns)
are *never* presented to the user, only the computer knows they exist.
[1] e.g. you order 1 x lifeboat but supplier later advises split
delivery of 1 x dingy, 6 x oars, 6 x life jackets, 1 x motor, etc.
Navigation:
[Reply to this message]
|