Re: Normalize or not?

It is not easy to follow...but I'll toss out some thoughts anyway.

I would generally not de-normalize the data model in order to make
referential integrity easier. If your "requirements" are that complex then
writing a wrapper around the insert/update layer for the tables in question
is a better idea. You can perform a higher level of validation without
sacrificing data integrity.

I would suggest looking at both the kinds of selection queries you plan to
run as well as what kinds of data (i.e., relationships between containers
and objects) to see how readily the final model will work. Two things that
SQL does have challenges with is indefinite hierarchies and many-to-many
relationships with constraints. You seem to be using both. It isn't that
SQL cannot handle them but as a programmer you need to be extra careful to
introduce appropriate constraints on data entry and to spend extra time
figuring out what valid combinations are likely to result in incorrect
results given the kinds of queries you are using.

There isn't anything wrong with the normal form violation but you probably
want to understand where your model is vulnerable to update anomalies. If
you can add some additional tables and turn the de-normalized data into
foreign-keys you can use referential integrity to at least limit your
exposure but it may not remove it completely.

In the end sometimes the only real question of import is whether the
solution works. If you are concerned then anything built upon the schema
should be designed such that you can refactor the underlying schema if you
desire. But it isn't necessarily worth it to exhaust every possible avenue
to find the better solution if you have one that works. The question
becomes whether you have the resources (time and/or money) to search for the
better solution now or whether you should just use the immediately viable
solution until it breaks.

David J.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Perry Smith
Sent: Thursday, April 14, 2011 7:29 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Normalize or not?

I hope this is reasonably easy to follow. I'm looking forward to your
thoughts and comments.

Thank you,
Perry Smith

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general