From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | "Devinder K Rajput" <Devinder(dot)Rajput(at)ipaper(dot)com>, joshua(at)iocc(dot)com |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: hiding normalization with views |
Date: | 2002-10-08 18:55:31 |
Message-ID: | 200210081155.32410.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2002-10-08 19:52:46 | Re: hiding normalization with views |
Previous Message | Joshua Daniel Franklin | 2002-10-08 18:48:40 | Re: hiding normalization with views |