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