Re: Re: Re: Re: Version Control in Database Applications

[perrin]

But how do you make an old version current? Change it's primary key? Nasty. And if you don't do that, you'll have to know which version is live, and that means your queries won't be so simple becuase they'll need to look up which version is live first and won't be able to assume it's a simple key with no appended number. Sounds like a tangle to me. I would go with the two-part key.

[samtregar]

Yes, of course my system requires that I update the key to implement revert. For example, let's say there are 10 versions of foo #1024: (1024, "1024,1" .. "1025,9"). To revert to version 7 I do:

DELETE FROM Foo WHERE id = "1024" OR id = 1024,9" OR id = "1024,8";
UPDATE Foo SET id = "1024" where id = "1024,7";

What's wrong here? You say it's "nasty" but I don't see anything particularly bad about it.

The thing is, reversion is an uncommon operation. It can be a little hairy and nothing bad will happen. There will only be one place in the code handling reversion. However, linking between objects in join tables and general querying will happen all over the place. If that requires two columns per table for every join I know we'll have problems.

-sam

[perrin]

I say it's nasty because if you have referential integrity constraints on other tables pointing to this, you won't be able to simply delete where id = "1024". Also, doesn't your sample lose all versions after 7? I expect people would want to keep those.

I still don't think multi-column keys are a big deal, especially if you hide them with a view. However, now I'm wondering if versions are really what you're after. What most people really mean when they say they want versioning of content is the ability to have a live version and an in-progress version. They couldn't care less about going back to old versions but they want to be able to put edits to the content through some kind of workflow process before making it live. If that's your situation, maybe you can get away with something simpler.

[samtregar]

Referential integrity constraints? Probably not. Most likely this will be a MySQL database. Does MySQL even have views? It didn't last time I worked with it, but I'm not fully up to speed on new developments.

Yes, this implementation would make reversion irreversable. I guess that might not be desirable although that's a better question for the clients than for me. I could implement a reversible reversion by creating a new version as a copy of the old one. That should be pretty easy to implement considering that the checkout routine will already need a cloning capability.

I think in this case my users really will want full versioning capabilities. The system they're using now has them and I think they've gotten pretty used to it. However, given what a pain this is to implement I think I can make a pretty strong argument that going with something simpler could save them a lot of money in development.

Thanks for the help,
-sam