SELECT *
FROM tablename
WHERE key = 12345
ORDER BY version;
[download]

SELECT *
FROM tablename
WHERE key LIKE '12345,%'
ORDER BY key;
[download]

SELECT *
FROM tablename o
WHERE key = ?
  AND version = (
        SELECT max(version)
        FROM tablename i
        WHERE i.key = o.key
)
[download]

SELECT *
FROM tablename
WHERE key = (
        SELECT max(key)
        FROM tablename
        WHERE key LIKE '$key,%'
)
[download]

Re: Speed. My guess is that given an indexed fixed-width CHAR field I should be able to get acceptable speed on lookups. All I need to do is decide on the maximum total number of objects and the maximum number of versions and I should be able to pick a field width. I'll be using MySQL and my memory is that MySQL indexes CHARs just as well as it does INTs.

Re: Querying. I think you misunderstood (or I did). In my planned implementation the current version would always exist without an appeneded ",version". Thus, selecting the current version is just:

SELECT * FROM foo WHERE id = "1024"

No sub-select required! And selecting version N is simply:

SELECT * FROM foo WHERE id = "1024,N"

Now, I may still need an extra column to store the current version number but as long as it isn't needed on every select and every join I'll be happy.

I think you're correct about collecting aggregate information across versions being hard. But I don't anticipate needing to do that very often, if at all.

-sam

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.