Re: 3NF question

"jonnie" <jsavell_at_gmail.com> wrote in message news:1105242337.662624.124020_at_c13g2000cwb.googlegroups.com...
>
> -CELKO- wrote:
> > There is no such thing as a"Magical, universal id number"; newbies
> use
> > IDENTITY or other proprietary auto-numbering devices to mimick a
> > sequential tape file when they do not understand RDBMS and keys.
>
> I respect what you have said. On the otherhand, I would like to say
> that I believe ID can be an acceptable attribute for an object, even if
> a sequential identity scheme is deemed unsuitable. Furthermore, I
> wouldn't mind making the Login/Password table employ an ID foreign key
> to refer to the ID property. When asked what benefit this expensive
> indirection offered, i could only say "maybe i get to have one or more
> login/password combinations for each user (feebly suggesting additional
> design freedom (ouch!) ) or, worse, that an ID would somehow be better
> protected by preventing its everyday use. Well, at least the FDs looked
> pretty, I thought.
>
> Additionally, I don't believe that one should reject a sequential
> identity scheme based soley upon a distaste for that which is
> proprietary (Yes, I enjoyed the BIBLE and I remember other good
> objections were also made). It seems reasonable that one could have no
> requirement placed upon ID generation other than that it occur quickly.
> And quick ID generation is desirable.
>
> And even if you insist upon the dismissal of proprietary Sequences, let
> us reflect upon alternatives.
>
> INSER INTO Foobar ( keycol , ... )
> VALUES ( COALESCE ( ( SELECT MAX ( keycold ) FROM Foobar ) , 0 ) + 1 ,
> ... );
>
> It might be the case that I am incorrect, but I don't see how several
> threads running the same operation woud never receive the same value
> returned by the subquery. I see a race condition here. But I don't
> know.
>
> I can only think that some sordid bits of PL/SQL or Transact SQL code
> might be needed to perform several operations corresponding to ID
> generation, uniqueness verification and possible storage. I haven't
> even come up with my own implementation yet.
> Eats, shoots, and leaves, and falls down,
> jonnie savell
>

Yes, there are appropriate times to use sequences for PKs, but it is a slippery slope. All too often, inexperienced (or even experienced) programmers resort to using them because they think its an easy way to do things. They should only be used when there is no natural key in the data, IOW, as a last resort. Purchase Order numbers are an example, and even them, I can come up with a system that would only require partial use of a sequence. I can't think of an occasion where speed of user ID generation is an overriding concern. Received on Sun Jan 09 2005 - 16:23:42 CET