Thread: DB design opinions - Foreign Key usage

Intro:
Hi all, my name is Vams, and I am fairly new to postgresql and totally new to
mailing lists, so please bare with me.  I have used hypersonic sql and mysql,
and now I am trying out postgresql... and so far, very impressive.  GJ dev
team.

Problem:
   For the location table, should I use two columns (ID PK, name) or just one
column (name PK)?
   Should I FK the id column from the location table or should I FK the name
column?
   Does postgresql keep a reference when using a FK (like pointers in C) or
does it actually make a copy and store it (like when C passes a copy in a
function call)?  If its the later, is there a way to reference a value in
postgresql (and have it auto-dereference during a query)?
   Finally, off topic, is there anyway (other than triggers) to keep the user
from inserting into the ID column when a sequence already takes care of it?
(I don't want someone adding id=5, name='blah' and when the sequence reaches
5, the insert will return error: can't have duplicates.)

Thank you all.


Situation:
   Two tables - bookstore and location.

   Bookstore has info on books and a column for location.  So, I use a foreign
key for the location column.  For Location, there are set of locations that
are described by _one_ word (downstairs, <city name>, warehouse, shop ...).
Now, the number of locations might change and so might their description word
(city name might become a warehouse name...).  But each location name will
always be unique.

Usage:
   The use for the tables is the end-user GUI will ask the user for book info,
but will provide a drop down list for the location input.  The drop down list
will be filled by a query from table location.

   I want it so that if the super DB manager ever changes one of the names in
the location table, the bookstore table will show the new value instead of
the old.  Obviously, the GUI will auto update the drop down list.

PS, I gave my support stuff at the bottom so that people will read the
question first and if they can help, they can choose to read the rest of the
post.  Again, thx.

On Sat, May 22, 2004 at 12:42:53 -0400,
  Vamsikrishna Mudrageda <gte181u@prism.gatech.edu> wrote:
> Intro:
> Hi all, my name is Vams, and I am fairly new to postgresql and totally new to
> mailing lists, so please bare with me.  I have used hypersonic sql and mysql,
> and now I am trying out postgresql... and so far, very impressive.  GJ dev
> team.
>
> Problem:
>    For the location table, should I use two columns (ID PK, name) or just one
> column (name PK)?

It generally isn't a good idea to make a name the primary key unless you
are very sure the names aren't going to change.

>    Should I FK the id column from the location table or should I FK the name
> column?

You should refer to the primary key when making foreign key references to
the table.

>    Does postgresql keep a reference when using a FK (like pointers in C) or
> does it actually make a copy and store it (like when C passes a copy in a
> function call)?  If its the later, is there a way to reference a value in
> postgresql (and have it auto-dereference during a query)?

The key is stored in the table. There are special triggers that make sure
that the key is actually in the referenced table.

>    Finally, off topic, is there anyway (other than triggers) to keep the user
> from inserting into the ID column when a sequence already takes care of it?
> (I don't want someone adding id=5, name='blah' and when the sequence reaches
> 5, the insert will return error: can't have duplicates.)

I think you can get this to happen with rules, but a before trigger is the
more normal way to modify rows being inserted. Note you will want to do
something for updates as well, since you can change the value of a primary
key in an update.