Re: referential integrity and defaults, DB design or trick

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