On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> wrote: > I've something like this: > > create table i ( > iid serial primary key, > name varchar(32) > ); > create table p ( > pid serial primary key, > iid int references i(iid) on delete cascade, > name varchar(32) > ); > create table c ( > bid serial primary key, > pid int references p(pid) on delete set null > ); > > insert into i values(default,'i'); > > -- default proprieties (singularity) > insert into p values(-1,null,'default p'); > > insert into p values(default,1,'this p'); > insert into p values(default,1,'that p'); > > insert into c values(default,null); > insert into c values(default,1); > insert into c values(default,-1); > > let's say I'd like to associate c with a name (the propriety) > > a null c.pid means I still have to assign a propriety or the > previously assigned propriety is not anymore available. > > I'd like to have a way to say take the propriety from i and the > above is what I came out with. > But that introduces a singularity. > > Any better design? I don't like to write a schema that needs data > inside to have a meaning. > > If not how can I protect the singularity from accidental delete? > Most of the db will be accessed through functions and this is a > step. > > An alternative design could be > create table c ( > bid serial primary key, > usedefault boolean, > pid int references p(pid) on delete set null > ); > where > usedefault=true -> use default > usedefault=false -> use i.pid > usedefault is null -> not yet assigned I still can't come up with something that satisfy my aesthetic. One way to use the second method would be to access the c table just through a function that will accept an int>0, int<=0 or null and convert it to * |<=0 use default, set usedefault=true, pid=null * >0 use pid set usedefault=false, pid=int * null set usedefault=null, pid=null opinions? Still I know it is very lightly related to postgres but any place I could learn something about DB design that have some example for postgres? thx -- Ivan Sergio Borgonovo http://www.webthatworks.it ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
