Re: hiding normalization with views - Mailing list pgsql-novice

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.

regards,

Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474



    
                    "Joshua Daniel
    
                    Franklin"                     To:     pgsql-novice@postgresql.org
    
                    <joshua@iocc.com>             cc:
    
                    Sent by:                      Subject:     [NOVICE] hiding normalization with views
    
                    pgsql-novice-owner@post
    
                    gresql.org
    

    

    
                    10/08/2002 12:26 PM
    

    

    




I have dealt with postgresql for the last couple of years
with a very simple database, but now I'm trying to do something
a bit more complicated and am having some trouble. Here's a situation:

I am wanting to manage a (postal) mailing list. Nearly everyone
lives in the same few cities, so to store things efficiently (and
minimize typos, etc) I have a few tables:

          Table "customers"
     Column     |  Type   | Modifiers
----------------+---------+-----------
 customer_id    | integer |
 creation_date  | date    |
 name           | text    | not null
 bill_address   | text    |
 bill_city      | integer |
 bill_state     | integer |
 bill_zip       | integer |
 alt_address    | text    |
 alt_city       | integer |
 alt_state      | integer |
 alt_zip        | integer |
 phone          | text    |
 alt_phone      | text    |
 contact_name   | text    |
Unique keys: customers_customer_id_key

        Table "cities"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 city   | text    |

and also "states" and "zips" that look just like "cities".
The problem is, whenever someone looks at or updates the data, I
want "customers.billing_city" and "cities.id" to be hidden and
automatically created when needed. I can create a view
that shows only the data I want, but that doesn't help for INSERTs
and UPDATEs.

Can anyone point me in the right direction or give an example?


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org