Re: Normalize or not?

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 <pedzsan(at)gmail(dot)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-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
>>>
>>