Message-ID: <434FC756.90901@brandywine.com>
Date: Fri, 14 Oct 2005 10:57:26 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: David Pradier <david.pradier@clarisys.fr>
Cc: Russell Simpkins <russellsimpkins@hotmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: Design problem : using the same primary keys for inherited
References: <20051014091829.GB19930@clarisys.fr>
	<BAY103-DAV1541E0C0650CC9BDA28204B57D0@phx.gbl>
	<20051014124334.GC19930@clarisys.fr>
In-Reply-To: <20051014124334.GC19930@clarisys.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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. 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.
> 
> 
> 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 int not null references role	-- 1
);

> David

> 

-- 
Daryl Richter
Director of Technology

((         Brandywine Asset Management          )
  ( "Expanding the Science of Global Investing"  )
  (          http://www.brandywine.com           ))