Re: Design problemi : using the same primary keys for inherited objects. - Mailing list 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
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
>