Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

> > Since I couldn't find an example like that in the (online as well as
> > printed) literature, is there a textbook (or online documentation)
> > about these issues? First, to quote it when I have to argue with
> > people, and second, to improve my own education.
>
> Unfortunately I cannot recommend any paper on this particular subject
> (dbdebunk is still a good reference),

I just had to find the right keywords to search for, in this case it's the term "overlapping foreign keys". It's actually addressed in some textbooks.

It's astounding imho that e.g. normalisation seems to be sufficiently addressed in database design classes, textbooks etc., while the problem of proper unification appears to be quite underrated, even ignored, even by lots of computer scientists by education. Which has also been confirmed by the number of answers I got in this group that apparently totally ignored the issue.

Thank goodness I had professors who had a clue (and taught me to always stay away from surrogate keys) even though I'm "just" an engineer.

Sincerely,

Wolfgang

P.S.: I consider the issue as closed. I am using an industry standard database model that has been established in cooperation by domain experts and (apparently less clueless) database developers and which I want (and need) to use as is. It matches the domain semantics really well and it's pretty complex (over 400 entities and likely to grow). It has been in development and use for over 10 years now and of course, transforming it and adding ridiculous kludges just to satisfy the professional ignorance of DB admins and "developers" while creating lots of incompatibility problems as well as potential sources for data inconsistency is totally out of question. Received on Wed Mar 13 2013 - 18:18:37 CET