Re: A problem in SQL (a real challenge)

"David Cressey" <david_at_dcressey.com> wrote:

>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.

     I didn't say it was a PK.

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

     My boss has a coherent view of his data. PK isn't required.

>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!

     Your point above is good, but...

>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.

     ...we don't. The data gets checked fairly thoroughly before invoicing.

     This is a client billing system, so the DE docs are internal. The client doesn't have much chance to exploit the potential loophole.

>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!

     Take another look at this sentence of mine: "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."

     Did I say it was a PK? No.

     The app is deployed and is working quite fine. The operators know what they are doing, my boss knows what he is doing, and I know what I am doing.

     Elegant theories are fine, but in the RW, sometimes, one has to do things a bit differently.

[snipped previous]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.