Re: 3NF question

-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 Received on Sun Jan 09 2005 - 04:45:37 CET