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.html and look for "Understanding Primary Keys, Foreign Keys and Constraints".

Ken suggests having a data dictionary and generating the schema from the dictionary. He has a PHP tool, being rewritten but with 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 can reference the ID in the UPDATE and DELETE statements, without fear of colliding with anything else. It's not so much a problem on small lookup tables, but on larger entities (people, companies, etc), referencing through the ID is much, much easier.

Hope that helps!
François Beausoleil