Re: referential integrity and defaults, DB design or trick

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