Search Postgresql Archives

referential integrity and defaults, DB design or trick

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux