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 but then I'll have to find a way to keep usedefault and pid coherent (I can't have usedefault=false and pid=null). And having a trigger doesn't make it look nicer, since if I pass not coherent values I'll have to deal with the error. The first technique doesn't have this problem (just others... but it is simpler). Any good link about DB design and how to deal with similar problems that has some postgres spice? thx and yep pgsql community is great. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
