Re: referential integrity and defaults, DB design or trick

On Wed, 19 Dec 2007 17:24:52 +0100
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> 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