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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Perry Smith Sent: Thursday, April 14, 2011 7:29 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
