Ah. I'm interfacing to the database via Ruby. So, write a function in pl/pgsql to do an insert, update, and delete that maintain the integrity that I want and then call those via Ruby.
On Apr 15, 2011, at 7:59 AM, David Johnston wrote: > More along the lines of pl/pgsql and/or whatever application language you are > using; not rules. > > On Apr 15, 2011, at 8:45, Perry Smith <pedz...@gmail.com> wrote: > >> Mostly, this entire project is for me to learn. What started out as a toy >> has gown into something that about 40 people use each day but it is >> definitely not normal production stress. Or course, I'd like to assume and >> do things as if it was going to be stressed. >> >> For whatever reason, I've done simple functions but I've never done complex >> wrappers like you are talking about. Just to be sure, when you say "writ[e] >> a wrapper around the insert/update layer", you are referring to the >> techniques documented in chapter 36 "The Rule System" ? >> >> That has always intimidated me but perhaps its time to grow. >> >> Thank you again, >> Perry >> >> On Apr 14, 2011, at 7:00 PM, David Johnston wrote: >> >>> 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-ow...@postgresql.org >>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Perry Smith >>> Sent: Thursday, April 14, 2011 7:29 PM >>> To: pgsql-general@postgresql.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@postgresql.org) To make >>> changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
