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?