Re: referential integrity and defaults, DB design or trick

On Thu, 20 Dec 2007 17:53:23 +0000
Richard Huxton <dev(at)archonet(dot)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