Re: hiding normalization with views

Devinder, Josh,

> I am assuming that data gets entered into the customers table and then
> should automagically update the cities, states, and zip_codes tables.  For
> example, if a new record or update contains the city "Chicago" and Chicago
> doesn't exist in cities, it should be added/inserted.  There might be a way
> to do this by setting up integrity constraints ( i don't know how), but you
> could also do it in code.  When a record is inserted/updated in the
> customers table, do a select with the city value in the cities table to see
> if the city exists.  if the city doesn't exist, insert it.  Do the same
> with states and zip codes.  This is some work and like I said, you might
> able to do it integrity constraints, but we need some help from the
> postgres gurus to see if that's possible.

Devinder is correct.  In fact, this is largely what the PostgreSQL RULES
system is for.  One can define a "FOR INSERT" RULE on a view, and then
program it in a procedural language on what to do with all of the data that
belongs in sub-tables.  See:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/rules.html

ALternately, you can do what I do in most of my applications, which is to
update data through functions or middleware rather than calling direct
INSERT/UPDATE statements.  This allows me to implement and maintain a large
array of complex business rules that would be difficult, or at least not
centrally managed, if I used RULES.
For example, instead of having the database client call:
INSERT INTO users ( first_name, last_name, logon, password )
VALUES ( $fname, $lname, $logon, $pwd );
it calls
SELECT fn_add_user ( $fname, $lname, $logon, $pwd );

Which approach you take (RULES, Functions, or 3rd-party middleware) depends
largely on your applicaiton specification; none is intrinsically superior.


--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco