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

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(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
> >
>
>
>
> ---------------------------(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