Re: A problem in SQL (a real challenge)

If it isn't unique, then by definition, it isn't the primary key. Primary key
is not one of those concepts that depends on which manager you talk to.

If it seems that way, then your enterprise needs more analysis to come up with
a more coherent view of the data.

I was once introduced to a billing system that allowed the same transaction to be entered more than once.
I was called in because they were having some troubles. After quietly deciding that "normalization" would be geek speak to the people I was talking to, I asked a simple question:

How do tell the difference between the same sale having been entered twice by mistake, and
the same customer placing two different orders for the same item on the same day?

The reply came back: "Funny you should ask about that. It's an ongoing problem, and we've never been able to sort this thing out. So, if a customer complains about a duplicate billing, we just issue a credit."

It turns out that this company's clients could grant themselves a two for one discount on any item in the catalog, just by placing two distinct orders for the same thing on the same day, and then complaining about duplicate billing when the invoices came in!

I sure hope what you are describing doesn't suffer from that loophole! But if you aren't in 1NF,
I suspect that there are other problems of the same nature.

I don't want to be a normalization purist, but if your primary keys aren't primary keys, then your database users "don't know what they are talking about" in a very real sense. And if you don't know what you are talking about, it's real hard to determine whether you are saying the right thing!

David Cressey, consultant
david_at_dcressey.com

Gene Wirchenko wrote in message <391b0d92.10121865_at_news.shuswap.net>...

> What if it is not abuse? I write/support a client billing system
>where it is possible to have more than one transaction with what might
>be considered the primary key. These are generally dealt with in a
>group. It is usually irrelevant to my boss which order they are
>listed in, but a transaction number can be entered to force a
>particular order. Whether the group has one transaction in it or more
>is irrelevant: all of the transactions will be consolidated for the
>invoice.
>
 [snip]
>
> I generally go with at least one key that is unique, but in the
>case above, it doesn't seem warranted.
>
Received on Fri May 12 2000 - 00:00:00 CEST