Re: hiding normalization with views

On Tue, 2002-10-08 at 12:26, Joshua Daniel Franklin wrote:
> 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?

3 *slightly* off topic comments:

Sometimes, you can go too "normalization happy". Yes, it's academically
correct, and you save a little disk space, but disks are cheap, and
you add bunches of extra joins to each query.

Since there are only 50 states, plus territories and Canada,
changing [bill|alt]_state to CHAR(2) is a good idea.

What are [bill|alt_zip? Are they designed to store the zip code,
or point to a zip code table? I think I'd change them to CHAR(10)
(so that Plus-4 and Canadian postal codes will fit).

--
+------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "they love our milk and honey, but preach about another |
| way of living" |
| Merle Haggard, "The Fighting Side Of Me" |
+------------------------------------------------------------+