hiding normalization with views

From: Joshua Daniel Franklin <joshua(at)iocc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: hiding normalization with views
Date: 2002-10-08 17:26:55
Message-ID: Pine.LNX.4.44.0210081203420.7247-100000@iocc.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Devinder K Rajput 2002-10-08 18:44:08 Re: hiding normalization with views
Previous Message Rod Kreisler 2002-10-08 03:25:25 Re: Creating exponential sequences