Message-ID: <BAY103-F36CD330B47F463E9DFAFC2B57D0@phx.gbl>
In-Reply-To: <20051014124334.GC19930@clarisys.fr>
From: "Russell Simpkins" <russellsimpkins@hotmail.com>
To: david.pradier@clarisys.fr
Cc: pgsql-sql@postgresql.org
Subject: Re: Design problem : using the same primary keys for inherited
	objects.
Date: Fri, 14 Oct 2005 11:53:54 -0400
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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