Re: Doubling the order

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 12:42:22 -0700

>> I don't see other way than to use identity column for order id? I
need it to connect products with order. What will you do? <<

Have you ever worked with paper forms? Audit trails? How many
accounting courses have you had before they let you write this system? I
am betting probably none.

In the old days, the forms were pre-printed and administered by a forms
control officer. Today they are electronic, but the same basic
accounting and forms control principles hold. The big difference is
that you are more likely to go to jail now (Sarbanes-Oxley laws, Enron
scandals, et al).

The order numbers had a check digit, a fixed length for display and some
information encoded in the number. This allowed us to validate the
order number -- compute the check digit, count the characters and look
at it for syntax, and we can immediately tell if it might be an order
number. Your "magical, all-purpose one-size-fits-all" IDENTITY column
has none of this audit stuff.

A missing order number was a major concern and had to be accounted for.
Your "magical, all-purpose one-size-fits-all" IDENTITY column has gaps
and I will bet you have no mechanism for doing an audit on missing
numbers either.

After that, we verify the order number. That, we go to a trusted source
and see that this "order form" was issued to the user who is submitting
it, we know the dates and places involved and that it meets any other
legal and business criteria.

Your "magical, all-purpose one-size-fits-all" IDENTITY column was simply
issued to a random user based on their insertion time at the table.
There is no was to track anything in the system.

Your "magical, all-purpose one-size-fits-all" IDENTITY column does not
move to another table or database easily, like a real key.

You are thinking that this is much harder than just making up "magical,
all-purpose one-size-fits-all" numbers on the fly. You're right!! It's
easy to be a doctor if the patient does not live, too.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages