Re: referential integrity and defaults, DB design or trick

[Erik Jones <erik(at)myemma(dot)com>]

On Dec 20, 2007, at 8:50 AM, Ivan Sergio Borgonovo wrote:

> 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

Ivan, after reading both of your posts I'm still not sure what you  
mean or are trying to do.  What do you mean by a singularity?  By  
propriety do you mean property?  Can you give an example with more  
descriptive names than i, p, and c?

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com