pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Design problem : using the same primary keys for inherited objects.
5+ messages / 3 participants
[nested] [flat]
* Re: Design problem : using the same primary keys for inherited objects.
@ 2005-10-14 12:43 David Pradier <david.pradier@clarisys.fr>
2005-10-14 14:57 ` Re: Design problem : using the same primary keys for inherited Daryl Richter <daryl@brandywine.com>
2005-10-14 15:53 ` Re: Design problem : using the same primary keys for inherited objects. Russell Simpkins <russellsimpkins@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: David Pradier @ 2005-10-14 12:43 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: pgsql-sql
> 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.
David
--
David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problem : using the same primary keys for inherited
2005-10-14 12:43 Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
@ 2005-10-14 14:57 ` Daryl Richter <daryl@brandywine.com>
2005-10-17 13:56 ` Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
1 sibling, 1 reply; 5+ messages in thread
From: Daryl Richter @ 2005-10-14 14:57 UTC (permalink / raw)
To: David Pradier <david.pradier@clarisys.fr>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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 ))
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problem : using the same primary keys for inherited objects.
2005-10-14 12:43 Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
2005-10-14 14:57 ` Re: Design problem : using the same primary keys for inherited Daryl Richter <daryl@brandywine.com>
@ 2005-10-17 13:56 ` David Pradier <david.pradier@clarisys.fr>
0 siblings, 0 replies; 5+ messages in thread
From: David Pradier @ 2005-10-17 13:56 UTC (permalink / raw)
To: Daryl Richter <daryl@brandywine.com>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
> >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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problem : using the same primary keys for inherited objects.
2005-10-14 12:43 Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
@ 2005-10-14 15:53 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-10-17 14:03 ` Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
1 sibling, 1 reply; 5+ messages in thread
From: Russell Simpkins @ 2005-10-14 15:53 UTC (permalink / raw)
To: david.pradier@clarisys.fr; +Cc: pgsql-sql
>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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problem : using the same primary keys for inherited objects.
2005-10-14 12:43 Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
2005-10-14 15:53 ` Re: Design problem : using the same primary keys for inherited objects. Russell Simpkins <russellsimpkins@hotmail.com>
@ 2005-10-17 14:03 ` David Pradier <david.pradier@clarisys.fr>
0 siblings, 0 replies; 5+ messages in thread
From: David Pradier @ 2005-10-17 14:03 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-17 14:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-14 12:43 Re: Design problem : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
2005-10-14 14:57 ` Re: Design problem : using the same primary keys for inherited Daryl Richter <daryl@brandywine.com>
2005-10-17 13:56 ` David Pradier <david.pradier@clarisys.fr>
2005-10-14 15:53 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-10-17 14:03 ` David Pradier <david.pradier@clarisys.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox