Skip site navigation (1) Skip section navigation (2)

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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Markus Bertheau <twanger(at)bluetwanger(dot)de>,pgsql-sql(at)postgresql(dot)org
Subject: Re: question about which column(s) are the right foreign key
Date: 2004-06-21 04:57:57
Message-ID: 200406202157.57676.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-06-21 05:35:09
Subject: Re: help with Postgres function
Previous:From: Josh BerkusDate: 2004-06-21 04:48:00
Subject: Re: use of a composite type in CREATE TABLE?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group