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

Hi,

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™.

I understand that both ways work and that the variant with just the
object id is probably both faster and smaller.

I'd be thankful for enlightenment :)

--
Markus Bertheau <twanger@bluetwanger.de>

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

В Пнд, 21.06.2004, в 06:57, Josh Berkus пишет:
> 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™"

Thanks. That really did provide the neccessary insight.

--
Markus Bertheau <twanger@bluetwanger.de>

As a follow up, I now have object versions with the combined primary key
(object id, date and time of object version creation). Now when I need
to refer to an object version in a foreign key, do I let the object id
column itself reference the objects table in addition to it being part
of the foreign key to the object versions table?

Thanks.

-- 
Markus Bertheau <twanger@bluetwanger.de>