Re: DB design advice: lots of small tables?

Le 2013-03-15 à 09:58, Thomas Kellerer a écrit :

> Kevin Grittner, 15.03.2013 14:36:
>> <soapbox-rant>
>> I occasionally hear someone maintaining that having a meaningless
>> sequential ID column as the primary key of each table is required
>> by the relational model.  At those moments I swear I can actually
>> hear E.F. Codd turning in his grave.  It was a requirement of old
>> pre-relational databases from the 60's and 70's, and some equally
>> primitive ORMs still like to have one, but a big point of
>> relational databases is that you don't need to navigate artificial
>> linkages between tables -- the relationship can generally be
>> determined by the fact that they contain common data elements.  If
>> these are natural, meaningful values which are visible to the user
>> it often allows complex queries to be much better optimized, since
>> they aren't forced through a single navigational linkage.
>> </soapbox-rant>
>
> You might be interested in a discussion regarding this topic on comp.databases.theory:
>
> https://groups.google.com/forum/?fromgroups=#!topic/comp.databases.theory/mqZZw3ojnjA

Along those lines, I love what Kenneth Downs says on his blog, The Database Programmer. Start at
http://database-programmer.blogspot.ca/2010/11/database-skills.htmland look for "Understanding Primary Keys, Foreign
Keysand Constraints". 

Ken suggests having a data dictionary and generating the schema from the dictionary. He has a PHP tool, being rewritten
butwith very slow progress. 

Keeping a meaningless ID is not a problem in and of itself. It makes it easier to edit records from the UI, since you
canreference the ID in the UPDATE and DELETE statements, without fear of colliding with anything else. It's not so much
aproblem on small lookup tables, but on larger entities (people, companies, etc), referencing through the ID is much,
mucheasier. 

Hope that helps!
François Beausoleil