Re: Design problemi : using the same primary keys for inherited objects.

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(at)spam(dot)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(at)spam(dot)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(at)spam(dot)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(at)spam(dot)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(at)spam(dot)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(at)spam(dot)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(dot)pradier(at)clarisys(dot)fr> wrote in message
news:20051014091829(dot)GB19930(at)clarisys(dot)fr(dot)(dot)(dot)
> 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
>