Re: identity columns
Date: Fri, 11 Jan 2002 11:57:14 GMT
> If an invoice number 12 and number 14 is registered, so should an invoice
> number 13 be registered. It thus serve an additional purpose than to order
> and identify: it reflects a "business rule". This rule can not be
> by the "natural key", unless a strict 1-1 is demanded between orders and
Your point is a very good one. I think the "no gaps" rule is applied, consciously or unconsciously, again and again in the world of business processing. What follows should be taken in that light.
One view of invoices is to see them as a set. When viewing them as a set, the invoice number has a feature that is indispensable for use as a primary key. Invoice numbers have identity. That is, two different invoice numbers never look alike. However, to determine "gaps" or their absence, you need to rely on another feature that invoice numbers have, namely sequence. What I'm calling "sequence" might really be two features, namely "order" and "interval". I'm not sure.
In any event, the fact that 13 is the successor of 12 and the fact that 14
is greater than twelve, is inherent to the reasoning that says "if 12 and 14
are present, but 13 is not present, it means there is a gap." Now I will
in turn, depends on the following rule: "Invoices are issued in a sequence." And that in turn is a small departure from viewing invoices as a set and only as a set.
I'm pointing this out because I think that an excessive demand that everything we do be expressed in set theory sometimes leads us to overlook valuable possible design features like gap detection. We all know the opposite tendency: people who have worked for years with sequential files, and can't think of a table as a set. But sometimes "sequence" is a more useful concept than "set" after all. Only sometimes, of course.
I say the above because Joe tends to harp on beginners who aren't thinking in terms of sets when they should be. It's part of bringing beginners up to speed. But sets aren't the only mathematical or ontological concept that is useful in IT.
-- Regards, David Cressey www.dcressey.comReceived on Fri Jan 11 2002 - 12:57:14 CET