Re: A problem in SQL (a real challenge)

Philip Lijnzaad <lijnzaad_at_ebi.ac.uk> wrote:

>Gene> What if it is not abuse? I write/support a client billing system
>Gene> where it is possible to have more than one transaction with what might
>Gene> be considered the primary key. These are generally dealt with in a
>Gene> group. It is usually irrelevant to my boss which order they are
>Gene> listed in, but a transaction number can be entered to force a
>Gene> particular order. Whether the group has one transaction in it or more
>Gene> is irrelevant: all of the transactions will be consolidated for the
>Gene> invoice.
>
>Seems like a recipe for disaster to me: how can you ever know that two $100
>items are real separate items, rather than an error? Having a proper key
>(and enforcing it using a constraint) helps a bit against this. Of course if
>won't help against entering it erroneously, but that's another matter. And
>with the key at least it's easy to get of the duplicates in a portable way.

      How would you know anyway? The operator would have to enter the differencing value to make the records different. As the system is, my boss doesn't want the bother.

>Secondly, maybe you don't need or want that key right now, but needs have a
>habit of changing; so it's usually pays off to stay on the safe side (and
>anyway, what's there against having an extra key field? Most of your database
>will or should be set up fairly generously, if you expect any
>growth. Waisting a few bytes per row for a key should be neglible). As with
>trying to remove duplicate entries, it is tricky to assign a primary key
>'later on' when the need arises.

     I have the field, it was enabled, but it isn't now. The needs did change, but in the other direction.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

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