Wednesday, July 12, 2006

"Single column primary keys should be enough for anybody"

Apparently PragDave had the temerity to suggest at RailsConf that Rails could stand some improvement in some areas, such as supporting composite primary keys in ActiveRecord. Naturally, the first reaction of a huge Rails fan like Martin Fowler is to get to work figuring out how to implement this.

Whoops, sorry, no, that would be in some alternate universe where fanboyism isn't the most important technical prinicple for some people. Martin's real reaction was to write a rebuttal, the gist of which is, if Rails doesn't already support it, it can't be important.

It's sad when someone who's done some good work puts on the fanboy blinders. You used to get the same schtick from mysql.com back in the 3.x days. Remember the rants in their docs about how foreign keys and transctions were for wimps and real programmers didn't want them anyway?

Of course, today even mysql corporate admits that these are important features, even though one can be forgiven for having a sneaking feeling that they don't really understand exactly why some people make such a big deal over it. Presumably once Rails supports composite primary keys we will hear Martin singing a different tune. One can only hope it won't be the sort of half-assed bullet point it is for MySQL (where you can have transactions and FKs and so forth, all right, as long as you don't use the default table type).

Anyway. Josh Berkus (a member of the postgresql core team) recently wrote a thorough, three-part series on exactly this subject (composite primary keys, if you recall):

If you're a typical I-prefer-to-not-think-about-the-database-until-something-goes-wrong developer, this series touches on a lot of issues that will really make you think.

Oh, and if you couldn't guess what Josh thinks of the "just use a surrogate [integer] key" approach...

The surrogate numeric key has been a necessary evil for as long as we've had SQL. It was set into SQL89 because the new SQL databases had to interact with older applications which expected "row numbers." It's a bit of legacy database thinking that, according to a conversation with Joe Celko, E.F. Codd regretted allowing to creep into the SQL standard.

Inevitably, practices which are "necessary evils" tend to become "pervasive evils" in the hands of the untrained and the lazy. Not realizing that ID columns are a pragmatic compromise with application performance, application developers with an shallow grounding in RDBMSes are enshrining numeric IDs as part of the logical and theoretical model of their applications. Worse yet, even RDBMS book authors are instructing their readers to "always include an ID column," suturing this misunderstanding into the body of industry knowledge like a badly wired cybernetic implant.

Some eye-opening stuff there.

(And I don't want this to be a my-ORM-is-better-than-yours post -- really! -- but I know somebody will ask, so yes, SQLAlchemy does support composite primary keys.)

18 comments:

Anonymous said...

I think you need to re-read Martin's article again as I didn't take it the same way you did. I took it as Martin feels that RoR is trying to be good at providing solutions to simple problems and the way he see it is that simple problems don't need to use composite primary keys. He feels that more complex problems should be handled by some other Ruby libraries/frameworks.

Anyway, I'm no Ruby fan so I could care less as to whether or not RoR supports this feature.

Jonathan Ellis said...

"He feels that more complex problems should be handled by some other Ruby libraries/frameworks."

That's just a dodge, and Martin knows it. ActiveRecord/Rails achieved critical mass quickly enough that there is not an ecology of real choice in Ruby there the way there is in Python, and probably never will be. Which is more of a strength than a weakness, if you ask me, but pretending that there's some advanced Ruby ORM out there that he can throw all the hard problems at is specious.

And while it's true that sometimes adding features isn't worth the complexity, for a real solution, "easy things should be easy and hard things should be possible." Adding support for CPK wouldn't make Martin's simple cases any more complex, but it *would* make other cases possible where they now are not.

This is total rationalization, not an honest case of a power/complexity tradeoff.

Anonymous said...

From a pure relational model POV, having an ID in each table is meaningless but then SQL and RDBMS are not really true relational model implementation either.

From an ORM POV (and I dislike ORMs) this makes lots of sense though since in the object oriented model each object have a unique ID. This ID is meaningless in regards of the information carried by the object but not in order to handle the object.

Jonathan Ellis said...

I don't understand your point; it is no more difficult for a properly written ORM to generate code for a CPK than it is for an int PK.

Anonymous said...

(Different from any earlier anonymii)
Really, I got a totally different feel from the article. If that is what fan-boi-ism is in the Rails community, it's news to me.
Honestly, he sounds more open-minded than you do. If you think that Rails' success precludes the success of other Ruby frameworks, I think you are in for a surprise. Rails was, last I checked, Open Source, which means if someone wants the complexity of composite keys, they can fork Rails. If enough people desire this feature, then the fork will become just as popular as Rails.
I think the point you are purporting to push is correct, but your argument is a straw man. Martin certainly doesn't state that "Single column primary keys should be enough for anybody". The only way you would have a rational defense of that claim would be if he insisted that not only was Rails the only viable Ruby framework, but that it was also the only viable framework in any language. He does neither, in fact, he recruits for the development of other frameworks in Ruby.

Jonathan Ellis said...

Speaking of straw men, "you can always fork it" is certainly much more straw-ier than anything I said!

It's incredibly hard to fork a high-profile project; the social pressure against such is enormous. Rightfully so; the price of incompatibility is usually far more than the benefits of your new codebase.

Aaron Bentley said...

Your statement "pretending that there's some advanced Ruby ORM out there that he can throw all the hard problems at is specious" doesn't make sense to me. Fowler's post goes on to discuss rBatis, whose ORM does support things like compound keys and other advanced SQL usage. It clearly exists, so who's pretending?

Jonathan Ellis said...

"[rBatis] clearly exists"

You have a much lower bar for accepting a project as non-vapor than most of the world.

mike bayer said...

fowlers "just let other ruby frameworks handle the harder stuff" was a completely ludicrous statement. there will never be another framework for Ruby (that is, one which more than twenty people ever use). For all of rails' advantages and breaths of fresh air it gives to java developers who dont remember/know what it was like to do large scale perl TMTOWTDI-style programming (hint: starts out great, ends up a nightmare), it remains a seriously kool-aid powered platform...and if the kool-aid ever runs out, ruby will be out the door along with rails.

Anonymous said...

I indeed read Martin's response, and it was even worse than I had thought. Not only RoR fanboyism, but he does it with analogies to The Matrix. Just ... wow. Terrible.

RoR might be inadequate for integrating into existing database apps, but as for starting a new one... I'm not sure the PK problem really is one, from the POV of object-relational database storage. In most languages except perhaps pure functional ones, objects have an opaque identity key, and this maps just fine to the single-key approach. Yes, surrogate integer keys are a crock and a kludge, but frankly so are ORMs in general.

Basically, AR is just inadequate as a general purpose RDBMS abstraction or even as a maximally flexible ORM such as Hibernate or SQLAlchemy ... but has it ever really pretended to be one? RoR has more glaringly obvious shortcomings than the fact that its ORM capabilities have a limited scope.

Aaron Bentley said...

You have a much lower bar for accepting a project as non-vapor than most of the world.

Perhaps. I go to the linked blog, I see an SVN URL and a description of the demo app, instructions for signing up on the mailing list. It doesn't sound like it's done, but it doesn't sound like vapour either.

Jonathan Ellis said...

It's a long way from being done, and the _only_ checkins are from May 20. Smell the fumes! :)

mike bayer said...

i found this pretty humorous:


You should not use iBatis unless you really have to: use ActiveRecord instead. I warn you, do not underestimate how much more work it can be to do manual O/R mapping. Or as Chad Fowler so eloquently put it on Rails Core:

Jon, I like what you’re doing, but by God I hope I never have to use it.


this seems like that attitude which says "ANYTHING thats not an active record/declarative pattern is unbelievably hard no matter what". therefore, we arent going to bother trying to make this reasonably useable since its impossible anyway.

Jon, you can do better than this! Data mapper should not be feared. Just read Fowler's book (back before he was a Rails spokesbot).

Dr Nic said...

For anyone does need Composite Primary Key support, I've started a gem that extends ActiveRecords via the set_primary_keys class method.

http://compositekeys.rubyforge.org

Composite foreign key support coming in the future.

Cheers
Nic

Jamis Buck said...

Note that the three-part series of articles you reference are not saying that surrogate primary keys are bad--merely that their misuse is bad. It was a good read, and something that more people should be aware of, but it certainly did not imply that no one should be using surrogate primary keys. Your reference to that article was misleading, to say the least.

Jonathan Ellis said...

Jamis, since one logical corrollary of "misuse of SPKs is bad" is "CPKs should often be used where many people currently use SPKs" (and this is explicit in the linked articles), I'm confused as to how you feel mislead.

Anonymous said...

Your portrayal of the referenced posts as one sided is inaccurate. Regarding tools like AR: "Usually Good." Of course, it can be pretty difficult to write a story if you're required to report all the facts. We understand.

Jonathan Ellis said...

"Your portrayal of the referenced posts as one sided is inaccurate."

What is this, incompetent rhetorical flourishes day?

Nowhere did I claim that SPKs are never useful, or that Josh Berkus's articles did. Pretending that I did in order to better attack me is a cheap trick. Even Jamis there recognises that yes, the article is about the mis-use of SPKs where a natural CPK would be better.