an important article (well, a blog post that links to an important article) against the usage of surrogate primary keys:
http://spyced.blogspot.com/2006/07/single-column-primary-keys-should- be.html On Sep 19, 2006, at 11:51 PM, Larry wrote: > > Joe wrote: >> <snip> >> it is generally preferable to use "natural" or "standard" IDs as >> primary >> keys. Since the page title in the wiki is unique it's a "natural" >> choice. >> <snip> >> >> Joe > > For almost every project where we used natural primary keys, I've > needed to later add some utility to allow the user to alter them. > This > code needs to look accross every table that reference this primary key > and alter it there. Running it without concurrency problems is > difficult and so dealing with the entire issue is just extra work > and a > headache. It also introduces another user interface element that I > have to train to users (key changes must be done in single user mode > using this special key change menu item, blah-blah-blah). So a while > back, I switched to never using natural primary keys. I've also heard > that integers are more efficient but it's not the reason I use them. > > Surogate primary keys are also the most common pattern I see from > experienced DBAs/developers/teams. SQLObject even defaults that way > not because it is more simple, but I suspect because Ian and others > must think it is good practice. > > Even on the wikipedia page you reference, it says, "Having the key > independent of all other columns insulates the database relationships > from changes in data values or database design (making your database > more agile) and guarantees uniqueness. Some database designers use > surrogate keys religiously regardless of the suitability of other > candidate keys, while others will use a key already present in the > data, if there is one." > > That said, I don't like my tools to place unecessary restrictions on > me. I like that SQLAlchemy will work equally well with databases that > I designed years ago, before I saw the light and stopped using natural > primary keys. I still like to design my database in the database > and I > find it trivial to manually add my own integer primary keys to every > table. I'm more of a fan of autoload=True and SQLSoup than I am of > ActiveMapper although I see the advantage of a Django Data Model > Driven > framework (or TG-FastData) for standing up new systems with no legacy > to worry about and I see how ActiveMapper is a necessary part of that. > That's just not usually the space where I usually work and SQLAlchemy > is the ideal tool for either situation. > > Larry Maccherone > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss -~----------~----~----~----~------~----~------~--~---
