Re: question about which column(s) are the right foreign key

Markus,

> I have objects in my database, and they have an object id generated with
> a sequence. Then I have object versions. The ids of object versions need
> to be unique only within one object id. But for simplicity they're
> generated with a sequence, too.
>
> Now I want to reference an object version. I can use just the object
> version id, because it "happens" to be globally unique. Conceptually
> though, I should use the object's id and its version's id.
>
> Now redundancy is Not Good™, so I wonder which way is the Right One™.
>

Well, conceptually, you should have generated a numerical version id for each
object version which would have told you the sequence in which that version
was created, i.e. version #1 of object 23421, version #2 of object 23421,
etc. This can be automated a number of ways, although it does require
locking the object during a version save.

The problem with the setup you have now is that you have an Object ID, which
doesn't intrinsically mean anything, and an Object Version ID, which also
doesn't tell you anything about the object or the version. If you want to
keep information about which "edition" of an object this particular
object-version is, you'll have to add a column -- which will then make the
object-version id redundant, since the table will then have two keys.

That's "the Right One™"

The concept probably nobody ever told you is that, in relational DB design,
you want to minimize the number of columns in your database that contain no
real data and exist only for internal purposes. Ideally, one would
construct a database in which no surrogate keys or sequences at all; but
performance and query-writing considerations make that impossible.

However, if fixing this issue is not an option, I'd just use the
object-version id as my FK. Unless, of course, you think you might fix the
problem later.

--
Josh Berkus
Aglio Database Solutions
San Francisco