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