|
|
Posted by Erland Sommarskog on 10/13/05 00:40
Trevor Best (nospam@localhost.invalid) writes:
> 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:
Dunno. With something like 500 tables in the data model I maintain, I
guess that it qualifies as "more than a simple system". And, no, not
all of them have one-column keys.
> 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:
I don't know the business domain well enough, so I prefer to not comment
on the details.
But I was once in the situation where I had a table with a four-column
key, and a need arose for a subtable with two more keys. So, I added
a surrogate to the existing table.
Much later I had reason to write code to maintain data in those tables,
and the UPDATE/INSERT for the subtable were complex. Because of the
surrogate key.
That surrogate key is no more by the way. (But for other reasons, two
other key levels have also gone away.)
On the top of my head I know we have a table with a five-column key. But
I would shudder for a seven-colunm key if it was the natural one.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|