pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDesign problemi : using the same primary keys for inherited objects.
5+ messages / 4 participants
[nested] [flat]
* Design problemi : using the same primary keys for inherited objects.
@ 2005-10-14 09:18 David Pradier <david.pradier@clarisys.fr>
0 siblings, 2 replies; 5+ messages in thread
From: David Pradier @ 2005-10-14 09:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problemi : using the same primary keys for inherited objects.
@ 2005-10-14 11:18 Russell Simpkins <russellsimpkins@hotmail.com>
parent: David Pradier <david.pradier@clarisys.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Russell Simpkins @ 2005-10-14 11:18 UTC (permalink / raw)
To: David Pradier <david.pradier@clarisys.fr>; pgsql-sql
----- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problemi : using the same primary keys for inherited objects.
@ 2005-10-14 15:29 codeWarrior <gpatnude@hotmail.com>
parent: David Pradier <david.pradier@clarisys.fr>
1 sibling, 1 reply; 5+ messages in thread
From: codeWarrior @ 2005-10-14 15:29 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problemi : using the same primary keys for inherited objects.
@ 2005-10-17 14:13 David Pradier <david.pradier@clarisys.fr>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Pradier @ 2005-10-17 14:13 UTC (permalink / raw)
To: codeWarrior <gpatnude@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Design problemi : using the same primary keys for inherited
@ 2005-10-17 16:57 Richard Huxton <dev@archonet.com>
parent: David Pradier <david.pradier@clarisys.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-10-17 16:57 UTC (permalink / raw)
To: David Pradier <david.pradier@clarisys.fr>; +Cc: codeWarrior <gpatnude@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-17 16:57 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 09:18 Design problemi : using the same primary keys for inherited objects. David Pradier <david.pradier@clarisys.fr>
2005-10-14 11:18 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-10-14 15:29 ` codeWarrior <gpatnude@hotmail.com>
2005-10-17 14:13 ` David Pradier <david.pradier@clarisys.fr>
2005-10-17 16:57 ` Re: Design problemi : using the same primary keys for inherited Richard Huxton <dev@archonet.com>
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