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

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.l.johnson@cox.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"                |
+------------------------------------------------------------+