Thread: Design problemi : using the same primary keys for inherited objects.
Hi everybody, i've got a strange design question to ask you. It's something I couldn't answer to while feeling confusely it was an absolutely BAD thing to do. For our application, we have developed our own framework which sits on top of PostgreSQL. It uses object programming and implements heritage. Inherited objects use heritage links in the framework and relation links in PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?). I've got this thing : An object A inherits from an object B, which inherits from a object C, which inherits from an object D. One of my colleagues proposed that we don't use serial (integer + sequence) primary keys for these objects, but that we use the very same integer primary keys. That is : the instance A would use the id 12343, and the instance B the same id 12343 and the instance C the same id 12343 and the D instance the same id 12343. It's possible as two instances of an object never inherit from a same instance of another object. The id seems to me absolutely bad, but I wouldn't know how to phrase why. Any suggestion ? Thanks in advance, David. -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
Re: Design problemi : using the same primary keys for inherited objects.
From
"Russell Simpkins"
Date:
----- Original Message -----
i've got a strange design question to ask you.
It's something I couldn't answer to while feeling confusely it was an
absolutely BAD thing to do.
For our application, we have developed our own framework which sits on
top of PostgreSQL. It uses object programming and implements heritage.
Inherited objects use heritage links in the framework and relation links
in PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?).
I've got this thing :
An object A inherits from an object B, which inherits from a object C,
which inherits from an object D.
One of my colleagues proposed that we don't use serial (integer +
sequence) primary keys for these objects, but that we use the very same
integer primary keys.
That is : the instance A would use the id 12343, and the instance B the
same id 12343 and the instance C the same id 12343 and the D instance the
same id 12343.
It's possible as two instances of an object never inherit from a same
instance of another object.
The id seems to me absolutely bad, but I wouldn't know how to phrase
why.
Any suggestion ?
Thanks in advance,
David.
Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. That being said, only you and your team can decide if more than one object will extend a base class. If you were doing something more like this
person -> sweepstakes entry
to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contest the user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes, since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial data type in both person and sweepstakes along with the foriegn key in sweepstakes from person.
The answer depends on the need. Hope that helps.
Russ
> Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. Thatbeing said, only you and your team can decide if more than one object will extend a base class. If you were doing somethingmore like this > person -> sweepstakes entry > to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contestthe user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes,since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial datatype in both person and sweepstakes along with the foriegn key in sweepstakes from person. > The answer depends on the need. Hope that helps. Thanks Russ, but well... It doesn't help me a lot. Our needs seem to allow that we use an id as primary key and foreign key at the same time. What i fear more is that it be against a good database design practice, because leading to potential problems. I give a clearer example : CREATE TABLE actor ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 ) CREATE TABLE person ( id_person INTEGER PRIMARY KEY REFERENCES actor, arg3 type3, arg4 type4 ) Don't you think it is a BAD design ? If it isn't, well, it will expand my database practices. David -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
David Pradier wrote: >>Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. Thatbeing said, only you and your team can decide if more than one object will extend a base class. If you were doing somethingmore like this >>person -> sweepstakes entry >>to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contestthe user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes,since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial datatype in both person and sweepstakes along with the foriegn key in sweepstakes from person. >>The answer depends on the need. Hope that helps. > > > Thanks Russ, but well... > It doesn't help me a lot. Our needs seem to allow that we use an id as > primary key and foreign key at the same time. > What i fear more is that it be against a good database design practice, > because leading to potential problems. > > I give a clearer example : > > CREATE TABLE actor ( > id_actor serial PRIMARY KEY, > arg1 type1, > arg2 type2 > ) > > CREATE TABLE person ( > id_person INTEGER PRIMARY KEY REFERENCES actor, > arg3 type3, > arg4 type4 > ) > > Don't you think it is a BAD design ? > If it isn't, well, it will expand my database practices. > It *is* a bad design. You should not do this. After all, how is that any different than this? CREATE TABLE actor_person ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 arg3 type3, arg4 type4 ) Furthermore, inheritance is almost certainly the wrong relationship type here. Normally, Actor would be a Role that a Person would be playing: create table role( id serial primary key, -- 1 name char(20) not null -- "Actor" ); create table person( id serial primary key, -- 1 name char(20) not null, -- "David" role_id intnot null references role -- 1 ); > David > -- Daryl Richter Director of Technology (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com ))
I dont consider this to be a design problem... In fact... I do the inherited
table thing pretty routinely... It (table inheritance) works very well if
you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
system without the overhead of managing transactions.
Consider the relationship between a company (sys_client) and the company's
employees (sys_user). An inheritance model in postgreSQL is a very efficient
mechism to ensire that user entries "roll-up-to" or are "owned" by a client
entry in the parent table. Here's a short example:
If you wish -- you can execute the following DDL.
CREATE TABLE sys_client (
id SERIAL NOT NULL PRIMARY KEY,
c_name VARCHAR(72),lu_client_type INTEGER NOT NULL DEFAULT 4 REFERENCES lu_client_type,lu_support_program INTEGER NOT
NULLREFERENCES lu_support_program(id),
create_dt TIMESTAMP NOT NULL DEFAULT NOW(),change_dt TIMESTAMP NOT NULL DEFAULT NOW(),change_id INTEGER DEFAULT
0,active_flagBOOLEAN NOT NULL DEFAULT TRUE
) WITH OIDS;
CREATE TABLE lu_user_type (
id serial NOT NULL PRIMARY KEY,type_desc varchar(72) NOT NULL,can_delete bool DEFAULT false,
create_dt timestamp NOT NULL DEFAULT now(),change_dt timestamp NOT NULL DEFAULT now(),change_id INTEGER NOT NULL
DEFAULT0,active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
INSERT INTO lu_user_type(type_desc) VALUES ('Administrator');
INSERT INTO lu_user_type(type_desc) VALUES ('User');
INSERT INTO lu_user_type(type_desc) VALUES ('Restricted user');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo - admin');
CREATE TABLE sys_user (
sys_client_id INTEGER NOT NULL REFERENCES sys_client(id),lu_client_group INTEGER references sys_client_group(id),
lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),f_name VARCHAR(50) NOT NULL,m_name VARCHAR(50),l_name
VARCHAR(50)NOT NULL,email_addr VARCHAR(120) NOT NULL,uname VARCHAR(20) NOT NULL,upwd VARCHAR(20) NOT NULL,
login_allowed BOOLEAN DEFAULT true,reset_pwd BOOLEAN DEFAULT false,pwd_change_reqd bool DEFAULT false, lost_passwd
boolDEFAULT false
) INHERITS (sys_client);
CREATE TABLE sys_user_history (
hist_id SERIAL NOT NULL PRIMARY KEY, hist_dt TIMESTAMP NOT NULL DEFAULT NOW()
) INHERITS (sys_user);
CREATE OR REPLACE RULE sys_user_history AS ON UPDATE TO sys_user DO INSERT
INTO sys_user_history (SELECT * FROM ONLY sys_user WHERE id = OLD.id);
CREATE TABLE sys_user_login (
id serial NOT NULL PRIMARY KEY,
sys_client INTEGER NOT NULL REFERENCES sys_client(id), login_date timestamp NOT NULL DEFAULT now(), valid_until
timestampNOT NULL DEFAULT (now() + '00:20:00'::interval), session_id varchar(32) NOT NULL UNIQUE,
create_dt timestamp NOT NULL DEFAULT now(), change_dt timestamp NOT NULL DEFAULT now(), change_id int4 NOT NULL
DEFAULT0, active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
insert into sys_client(c_name) VALUES ('Mattel');
insert into sys_client(c_name) VALUES ('Hasbro');
insert into sys_client(c_name) VALUES ('Lego');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 1, 'Arnold', 'Antione', 'Aardvaark',
'arnold@spam.com', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 2, 'Roberto', 'Guiterrez', 'Amendola',
'roberto@spam.com', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 1, 'Albert', '', 'Einstien',
'albert@spam.com', 'albert', 'albert');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 2, 'David', 'C', 'Davidson',
'david@spam.com', 'david', 'david');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 1, 'Marilyn', '', 'Monroe',
'mmonroe@spam.com', 'mmonroe', 'mmonroe');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 2, 'Fred', 'E', 'Flintstone',
'fred@spam.com', 'fred', 'fred');
SET SQL_INHERITANCE = ON;
-- RUN EACH OF THESE QUERIES IN SUCESSION TO SEE HOW IT REALLY WORKS...
SELECT * FROM sys_client;
SELECT * FROM ONLY sys_client;
SELECT * FROM sys_user;
SELECT * FROM ONLY sys_user;
SELECT SC.id AS client_id, SC.c_name, SU.id AS employee_id, SU.f_name,
SU.l_name FROM sys_client SC JOIN sys_user SU ON SU.sys_client_id = SC.id;
"David Pradier" <david.pradier@clarisys.fr> wrote in message
news:20051014091829.GB19930@clarisys.fr...
> Hi everybody,
>
> i've got a strange design question to ask you.
> It's something I couldn't answer to while feeling confusely it was an
> absolutely BAD thing to do.
>
> For our application, we have developed our own framework which sits on
> top of PostgreSQL. It uses object programming and implements heritage.
> Inherited objects use heritage links in the framework and relation links
> in PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?).
>
> I've got this thing :
> An object A inherits from an object B, which inherits from a object C,
> which inherits from an object D.
> One of my colleagues proposed that we don't use serial (integer +
> sequence) primary keys for these objects, but that we use the very same
> integer primary keys.
> That is : the instance A would use the id 12343, and the instance B the
> same id 12343 and the instance C the same id 12343 and the D instance the
> same id 12343.
>
> It's possible as two instances of an object never inherit from a same
> instance of another object.
>
> The id seems to me absolutely bad, but I wouldn't know how to phrase
> why.
>
> Any suggestion ?
>
> Thanks in advance,
> David.
>
> --
> David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de
> projet logiciels libres / open-source
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
Re: Design problem : using the same primary keys for inherited objects.
From
"Russell Simpkins"
Date:
>Thanks Russ, but well... >It doesn't help me a lot. Our needs seem to allow that we use an id as >primary key and foreign key at the same time. >What i fear more is that it be against a good database design practice, >because leading to potential problems. > >I give a clearer example : > >CREATE TABLE actor ( >id_actor serial PRIMARY KEY, >arg1 type1, >arg2 type2 >) > >CREATE TABLE person ( >id_person INTEGER PRIMARY KEY REFERENCES actor, >arg3 type3, >arg4 type4 >) > >Don't you think it is a BAD design ? >If it isn't, well, it will expand my database practices. That is perfectly valid. Only, I would argue that an actor is a person. What I was offering was dealing with issues where more then one actor could be the same person. Given your design, a person could only be one actor. If that is true, no more discussion is needed. If that is not true, then one way to deal with that is to make compound primary keys in your actor table. table person ( person_id serial primary key, name varchar(20)); table actor( person_id foreign key references person, role varchar(20), primary key ( person_id, role ) ); would then allow a person to be more then on actor based on role.
> >I give a clearer example : > >CREATE TABLE actor ( > >id_actor serial PRIMARY KEY, > >arg1 type1, > >arg2 type2 > >) > >CREATE TABLE person ( > >id_person INTEGER PRIMARY KEY REFERENCES actor, > >arg3 type3, > >arg4 type4 > >) > >Don't you think it is a BAD design ? > >If it isn't, well, it will expand my database practices. > > It *is* a bad design. You should not do this. After all, how is that > any different than this? > CREATE TABLE actor_person ( > id_actor serial PRIMARY KEY, > arg1 type1, > arg2 type2 > arg3 type3, > arg4 type4 > ) > Furthermore, inheritance is almost certainly the wrong relationship type > here. Normally, Actor would be a Role that a Person would be playing: Oups, I've made a vocabulary mistake. By 'actor', I meant "somebody who does something". Lots of tables inherits from 'actor' in our current design, each of these being a "job" : laboratory, delivering company, etc... Furthermore, 'person' inherits from 'actor', and some other tables inherits from 'person' : user, physician, customer, etc... Do you continue to think that inheritance is the wrong relationship type here ? > It *is* a bad design. You should not do this. After all, how is that > any different than this? Well, not every line of actor are in laboratory, as some are in person, and in some moreother tables. So, it is different, isn't it ? -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
> That is perfectly valid. Only, I would argue that an actor is a person. Oups, i really made a vocabulary mistake here. Let me paste what i wrote some minutes earlier to Daryl : ---- By 'actor', I meant "somebody who does something". Lots of tables inherits from 'actor' in our current design, each of these being a "job" : laboratory, delivering company, person, etc... Furthermore, 'person' inherits from 'actor' and some other tables inherits from 'person' : user, physician, customer, etc... ---- > What I was offering was dealing with issues where more then one actor could > be the same person. Given your design, a person could only be one actor. If > that is true, no more discussion is needed. If that is not true, then one > way to deal with that is to make compound primary keys in your actor table. > table person ( > person_id serial primary key, > name varchar(20)); > table actor( > person_id foreign key references person, > role varchar(20), > primary key ( person_id, role ) > ); > would then allow a person to be more then on actor based on role. It is specified that not two laboratories could be the same actor, for example. That's why i think having a primary key referencing another primary key could technically work, but well, it seems to me BAD to mix two primary keys together. I'm still wondering... -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
Well yes, i find your system very interesting and will maybe use it as
solution for another problem we have ( :-) ), but i fail to see where
it makes use of a primary key referencing another primary key ?
On Fri, Oct 14, 2005 at 08:29:40AM -0700, codeWarrior wrote:
> I dont consider this to be a design problem... In fact... I do the inherited
> table thing pretty routinely... It (table inheritance) works very well if
> you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
> system without the overhead of managing transactions.
>
> Consider the relationship between a company (sys_client) and the company's
> employees (sys_user). An inheritance model in postgreSQL is a very efficient
> mechism to ensire that user entries "roll-up-to" or are "owned" by a client
> entry in the parent table. Here's a short example:
>
> If you wish -- you can execute the following DDL.
>
> CREATE TABLE sys_client (
>
> id SERIAL NOT NULL PRIMARY KEY,
>
> c_name VARCHAR(72),
> lu_client_type INTEGER NOT NULL DEFAULT 4 REFERENCES lu_client_type,
> lu_support_program INTEGER NOT NULL REFERENCES lu_support_program(id),
>
> create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
> change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
> change_id INTEGER DEFAULT 0,
> active_flag BOOLEAN NOT NULL DEFAULT TRUE
>
> ) WITH OIDS;
>
> CREATE TABLE lu_user_type (
>
> id serial NOT NULL PRIMARY KEY,
> type_desc varchar(72) NOT NULL,
> can_delete bool DEFAULT false,
>
> create_dt timestamp NOT NULL DEFAULT now(),
> change_dt timestamp NOT NULL DEFAULT now(),
> change_id INTEGER NOT NULL DEFAULT 0,
> active_flag bool NOT NULL DEFAULT true
>
> ) WITH OIDS;
>
> INSERT INTO lu_user_type(type_desc) VALUES ('Administrator');
> INSERT INTO lu_user_type(type_desc) VALUES ('User');
> INSERT INTO lu_user_type(type_desc) VALUES ('Restricted user');
> INSERT INTO lu_user_type(type_desc) VALUES ('Demo');
> INSERT INTO lu_user_type(type_desc) VALUES ('Demo - admin');
>
> CREATE TABLE sys_user (
>
> sys_client_id INTEGER NOT NULL REFERENCES sys_client(id),
> lu_client_group INTEGER references sys_client_group(id),
>
> lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
> f_name VARCHAR(50) NOT NULL,
> m_name VARCHAR(50),
> l_name VARCHAR(50) NOT NULL,
> email_addr VARCHAR(120) NOT NULL,
> uname VARCHAR(20) NOT NULL,
> upwd VARCHAR(20) NOT NULL,
>
> login_allowed BOOLEAN DEFAULT true,
> reset_pwd BOOLEAN DEFAULT false,
> pwd_change_reqd bool DEFAULT false,
> lost_passwd bool DEFAULT false
>
> ) INHERITS (sys_client);
>
>
> CREATE TABLE sys_user_history (
>
> hist_id SERIAL NOT NULL PRIMARY KEY,
> hist_dt TIMESTAMP NOT NULL DEFAULT NOW()
>
> ) INHERITS (sys_user);
>
> CREATE OR REPLACE RULE sys_user_history AS ON UPDATE TO sys_user DO INSERT
> INTO sys_user_history (SELECT * FROM ONLY sys_user WHERE id = OLD.id);
>
> CREATE TABLE sys_user_login (
>
> id serial NOT NULL PRIMARY KEY,
>
> sys_client INTEGER NOT NULL REFERENCES sys_client(id),
> login_date timestamp NOT NULL DEFAULT now(),
> valid_until timestamp NOT NULL DEFAULT (now() + '00:20:00'::interval),
> session_id varchar(32) NOT NULL UNIQUE,
>
> create_dt timestamp NOT NULL DEFAULT now(),
> change_dt timestamp NOT NULL DEFAULT now(),
> change_id int4 NOT NULL DEFAULT 0,
> active_flag bool NOT NULL DEFAULT true
>
> ) WITH OIDS;
>
>
> insert into sys_client(c_name) VALUES ('Mattel');
> insert into sys_client(c_name) VALUES ('Hasbro');
> insert into sys_client(c_name) VALUES ('Lego');
>
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (1, 1, 'Arnold', 'Antione', 'Aardvaark',
> 'arnold@spam.com', 'arnie', 'arnie');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (1, 2, 'Roberto', 'Guiterrez', 'Amendola',
> 'roberto@spam.com', 'arnie', 'arnie');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (2, 1, 'Albert', '', 'Einstien',
> 'albert@spam.com', 'albert', 'albert');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (2, 2, 'David', 'C', 'Davidson',
> 'david@spam.com', 'david', 'david');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (3, 1, 'Marilyn', '', 'Monroe',
> 'mmonroe@spam.com', 'mmonroe', 'mmonroe');
> INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
> email_addr, uname, upwd) VALUES (3, 2, 'Fred', 'E', 'Flintstone',
> 'fred@spam.com', 'fred', 'fred');
>
>
>
> SET SQL_INHERITANCE = ON;
>
> -- RUN EACH OF THESE QUERIES IN SUCESSION TO SEE HOW IT REALLY WORKS...
> SELECT * FROM sys_client;
> SELECT * FROM ONLY sys_client;
> SELECT * FROM sys_user;
> SELECT * FROM ONLY sys_user;
>
> SELECT SC.id AS client_id, SC.c_name, SU.id AS employee_id, SU.f_name,
> SU.l_name FROM sys_client SC JOIN sys_user SU ON SU.sys_client_id = SC.id;
>
>
>
> "David Pradier" <david.pradier@clarisys.fr> wrote in message
> news:20051014091829.GB19930@clarisys.fr...
> > Hi everybody,
> >
> > i've got a strange design question to ask you.
> > It's something I couldn't answer to while feeling confusely it was an
> > absolutely BAD thing to do.
> >
> > For our application, we have developed our own framework which sits on
> > top of PostgreSQL. It uses object programming and implements heritage.
> > Inherited objects use heritage links in the framework and relation links
> > in PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?).
> >
> > I've got this thing :
> > An object A inherits from an object B, which inherits from a object C,
> > which inherits from an object D.
> > One of my colleagues proposed that we don't use serial (integer +
> > sequence) primary keys for these objects, but that we use the very same
> > integer primary keys.
> > That is : the instance A would use the id 12343, and the instance B the
> > same id 12343 and the instance C the same id 12343 and the D instance the
> > same id 12343.
> >
> > It's possible as two instances of an object never inherit from a same
> > instance of another object.
> >
> > The id seems to me absolutely bad, but I wouldn't know how to phrase
> > why.
> >
> > Any suggestion ?
> >
> > Thanks in advance,
> > David.
> >
> > --
> > David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de
> > projet logiciels libres / open-source
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
David Pradier wrote: > Well yes, i find your system very interesting and will maybe use it as > solution for another problem we have ( :-) ), but i fail to see where > it makes use of a primary key referencing another primary key ? As regards the issue of one primary-key referencing another, I can't see any problem. You want 0 or 1 references from table B to any row in table A - this does that without any fuss or complication. A primary key is a value (or set of values) like any other. It is unique over any one table, but nothing says it can't be the same as a value in some other table. The other way would be something like: CREATE TABLE A (id serial not null unique,aval text,primary key (id) ); CREATE TABLE B (id serial not null unique,aref int4 not null unique references A,bval text,primary key (id) ); So - in table B we now have two candidate keys (id, aref) and above I've chosen "id" as the primary-key. But I can eliminate "id" completely and not lose any of the meaning of table "B" - which tells me that I was wrong to think the rest of the table was dependent on "id". So, I must have been wrong in making "id" a primary-key and since it has no meaning of its own, I can eliminate it. CREATE TABLE B (aref int4 not null unique references A,bval text,primary key (aref) ); -- Richard Huxton Archonet Ltd