Thread: referential integrity and defaults, DB design or trick

referential integrity and defaults, DB design or trick

From

Ivan Sergio Borgonovo

Date:

19 December 2007, 15:15:27

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

Re: referential integrity and defaults, DB design or trick

From

Ivan Sergio Borgonovo

Date:

20 December 2007, 13:48:52

On Wed, 19 Dec 2007 17:24:52 +0100
Ivan Sergio Borgonovo <mail@webthatworks.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

Re: referential integrity and defaults, DB design or trick

From

Erik Jones

Date:

20 December 2007, 14:56:30

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@webthatworks.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@myemma.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

Re: referential integrity and defaults, DB design or trick

From

Ivan Sergio Borgonovo

Date:

20 December 2007, 16:22:23

On Thu, 20 Dec 2007 09:55:29 -0600
Erik Jones <erik@myemma.com> wrote:

>
> 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@webthatworks.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,

this should be cid for coherency in notation.

> >>     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?

OK... provided I'm not at risk of opening a flame war against bottom
posting ;)

First I beg you pardon for relying too much on my spell checker
(property and propriety in Italian are spelt the same, and missing
any red warning I went further).

Yep property as being yellow.

* i has a default property i.name "he is hungry by default"
i.name=hungry

* i has a set of other property he can chose from in p (thirsty,
happy...)

* c is the chosen property of the day (c actually will contain more
than a p)

c can contain:
1) sorry not chosen yet
2) pid
3) hey today I'm hungry as usual

I want to be sure that c can contain just the above.
1) and 2) are easy null + references p(pid)

storing 3) is a bit tricky (for me at least):

a) put a singularity in p that means 3) and that won't get overwriten
by serial (this means making the logic depends on data not on schema
and references...) + risk to lose/forget around that singularity
(the p row whose pid is -1).

b) add another column to c (usedefault) that split 1), 2) from 3)
but that oblige me to take care of coherence of values in pid and
usedefault and opening the possibility for not atomic operation on
(userdefault,pid).

I could write a trigger that:
for every change of pid try to adjust userdefault and for every
change of userdefault check if it is coherent and raise an error. But
then I'd have to handle the error.
I could access the table just through a function that map
_pid>0 -> usedefault=false, pid=_pid
_pid=null -> usedefault=null,pid=null
_pid<0 -> usedefault=true, pid=null

I was wondering if there is any standard technique to deal with
similar problems or some postgresish way to deal with them.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Re: referential integrity and defaults, DB design or trick

From

Richard Huxton

Date:

20 December 2007, 16:53:56

Ivan Sergio Borgonovo wrote:
> * i has a default property i.name "he is hungry by default"
> i.name=hungry
>
> * i has a set of other property he can chose from in p (thirsty,
> happy...)
>
> * c is the chosen property of the day (c actually will contain more
> than a p)
>
> c can contain:
> 1) sorry not chosen yet
> 2) pid
> 3) hey today I'm hungry as usual
>

It's still not entirely clear (to me, anyway)

Do you want something where every day, each user can:
1. Select their "default property"
OR
2. Select from a shortlist of other properties
OR
3. Not select anything

CREATE TABLE properties (
   pid serial,
   name text,
   PRIMARY KEY (pid)
);
CREATE TABLE user_default_property (
   uid int NOT NULL REFERENCES users,
   pid int NOT NULL REFERENCES properties,
   PRIMARY KEY (uid)
);
CREATE TABLE user_property_choices (
   uid int NOT NULL REFERENCES users,
   pid int NOT NULL REFERENCES properties
   PRIMARY KEY (uid)
);

That allows you to have 0 or 1 defaults per user and 0 or 1 choices per
user too.

--
   Richard Huxton
   Archonet Ltd

Re: referential integrity and defaults, DB design or trick

From

Sam Mason

Date:

20 December 2007, 17:10:53

On Thu, Dec 20, 2007 at 06:31:47PM +0100, Ivan Sergio Borgonovo wrote:
> On Thu, 20 Dec 2007 09:55:29 -0600 Erik Jones <erik@myemma.com> wrote:
> > 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@webthatworks.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,
> > >>     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?
>
> OK... provided I'm not at risk of opening a flame war against bottom
> posting ;)

I'll not complain about that anyway.

I still don't understand what "singularity" means though.  And your
table names haven't improved much.  I think you mean something like
this, but I'm not sure:

  CREATE TABLE props (
    name TEXT NOT NULL PRIMARY KEY,
    defvalue TEXT
  );

  CREATE TABLE items (
    name TEXT NOT NULL PRIMARY KEY
  );

  CREATE TABLE itemprops (
    itemname TEXT NOT NULL REFERENCES items,
    propname TEXT NOT NULL REFERENCES props,
    value TEXT
  );

  (I've gone to using natural keys after discussions on this list,
  especially in simple examples like this they help to concentrate the
  design on the essentials)

Assuming that's the case, you could represent the following as:

> c can contain:
> 1) sorry not chosen yet

no row in itemprop for this combination

> 2) pid

the value in itemprop is non-null

> 3) hey today I'm hungry as usual

the value in itemprop is null. use something like this to get the
current values for an item:

  SELECT i.itemname, i.propname,
    coalesce(i.value,p.defvalue) AS curval
  FROM itemprops i, props p
  WHERE i.propname = p.name
    AND i.itemname = 'desk';


Spelling out identifiers with longer names really helps *a lot* when
you're an outsider trying to understand someones code.  It also
short-circuits a lot of the ambiguity that will inevitably exist in the
description.


  Sam

Re: referential integrity and defaults, DB design or trick

From

Ivan Sergio Borgonovo

Date:

20 December 2007, 20:27:50

On Thu, 20 Dec 2007 17:53:23 +0000
Richard Huxton <dev@archonet.com> wrote:

> CREATE TABLE properties (
>    pid serial,
>    name text,
>    PRIMARY KEY (pid)
> );
> CREATE TABLE user_default_property (
>    uid int NOT NULL REFERENCES users,
>    pid int NOT NULL REFERENCES properties,
>    PRIMARY KEY (uid)
> );
> CREATE TABLE user_property_choices (
>    uid int NOT NULL REFERENCES users,
>    pid int NOT NULL REFERENCES properties
>    PRIMARY KEY (uid)
> );

> That allows you to have 0 or 1 defaults per user and 0 or 1 choices
> per user too.

Thanks, this led me to insist on an idea I abandoned mislead by bad
data quality, looking at written code, no matter how simple it is is
better than insisting in speculating in your mind hoping you'll avoid
refactoring.

The default property (that is actually made by several fields) in my
case is not completely homogeneous with the others, because it has a
double meaning.
It is cleaner to split the meanings and the data. This will even give
me a chance to avoid completely the concept of default property.

thanks, I think you put me on the right path.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Re: referential integrity and defaults, DB design or trick

From

Richard Huxton

Date:

21 December 2007, 07:19:19

Ivan Sergio Borgonovo wrote:
> The default property (that is actually made by several fields) in my
> case is not completely homogeneous with the others, because it has a
> double meaning.
> It is cleaner to split the meanings and the data.

It usually is. A lot of the design decisions you can regret a year or
two down the line are from trying to use one value for two purposes.

--
   Richard Huxton
   Archonet Ltd

Re: referential integrity and defaults, DB design or trick

From

Ivan Sergio Borgonovo

Date:

22 December 2007, 15:02:36

On Fri, 21 Dec 2007 08:19:08 +0000
Richard Huxton <dev@archonet.com> wrote:

> Ivan Sergio Borgonovo wrote:
> > The default property (that is actually made by several fields) in
> > my case is not completely homogeneous with the others, because it
> > has a double meaning.
> > It is cleaner to split the meanings and the data.
>
> It usually is. A lot of the design decisions you can regret a year
> or two down the line are from trying to use one value for two
> purposes.

Unfortunately it looked a nearly perfect solution till I didn't
discover there is some overlap of properties definitions.
One of the column of a default property is written somewhere else,
while a "not default" property has its own or doesn't have it at all.

I can't see any way to write an "on update cascade" somewhere in your
proposed schema.

Unfortunately I'm writing a plug-in and I've no complete control over
the schema.
I can't separate that column, put it in another table etc...

Decoupling the system as much as making all the angels of programming
sing in chorus, is going to make the design less maintainable than a
little hack.

/*
sort of...
*/

CREATE TABLE users (
   uid serial primary key,
   email varchar(64) unique -- I'can't move this away, not my table!
);

CREATE TABLE properties (
   pid serial,
   name text,
   email varchar(64), -- but not default proprieties have their own
   PRIMARY KEY (pid)
);
CREATE TABLE user_default_property (
   uid int NOT NULL REFERENCES users,
   pid int NOT NULL REFERENCES properties,
   PRIMARY KEY (uid)
);
CREATE TABLE user_property_choices (
   uid int NOT NULL REFERENCES users,
   pid int NOT NULL REFERENCES properties
   PRIMARY KEY (uid)
);

At this moment the best solution I can think of is hiding this behind
a function.

CREATE TABLE users (
   uid serial primary key,
   email varchar(64) unique -- I'can't move this away, not my table!
);

CREATE TABLE properties (
   pid serial primary key,
   name varchar(64),
   email varchar(64),
   otherstuff text,
);

create table user_property_choices (
  cid serial primary key,
  uid int NOT NULL REFERENCES users,
  assigned boolean,
  pid int NULL REFERENCES properties
);

if assigned=true  -> if pid=null use default
                  -> if pid!=null use pid
if assigned=false -> not yet assigned

better than using "usedefault" system since anyway there will be
something to pick up and at least I won't have to deal with
exceptions.

Thanks again.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it