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

On Mon, 11 Mar 2013 19:53:56 +0100
Wolfgang Keller <feliphil_at_gmx.net> wrote:

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

If you want to argue with people, database design is a fine place to start. ;-)

The design you've described

> deeply nested hierarchy (which corresponds very
> well to the semantics of the application domain

sounds like what sometimes happens when the designer understood object orientation better than the relational model.

> Due to the level of depth of the nesting, primary keys, especially for
> n:m(:o:...) tables can be long - I stopped counting at 15 columns or
> so.

Most objects in the real world can be identified by just a few attributes. If each of those 15 columns were just a single digit, you could describe 10^15 rows, or 1000 for every star in our galaxy. That suggests to me that the design could be simplified, perhaps with a flamethrower from a safe distance.

I suspect your instinct is right, and that the issue you've hit on is only the tip of the iceberg. If so, the question isn't how to identify the entities in your database, but how to choose the entities to identify. IOW how to design the database in the first place, the logical model.

What to read about that? Fabian Pascal has lately been updating his papers on database design. You might want to check http://www.dbdebunk.com/p/papers_3.html.

HTH. --jkl Received on Mon Mar 11 2013 - 22:41:03 CET