Re: Simple question on data design...

Ken Zigler wrote:
>
> Hello, group.
>
> Consider tables with columns that contain data from
> a limited, pre-set list or domain, such as:
>
> tblAddresses (AddressID, ClientID, Street, City, State, Zip, AddressType)
>
> where the attribute AddressType could be one of the following:
>
> Primary
> Billing
> Shipping
> Location
>
> (disregarding whether this is a good model or not) ...one could store
> the data as shown above, or each value could be represented
> by a single letter. The single character value could save space,
> but requires more work when displaying the data in a meaningful
> way (by substituting the word represented)... Other similar scenarios
> would include attributes like ShippingMethod, PaymentType, Gender,
> etc...
>
> Is there any logic that suggests one approach over the other?

A big advantadge of using a separate table is that you decouple the description from the data itself. If somebody (important enough!) wanted to see Main instead of Primary for AdressType, you simply update on row in the table with the descriptions and not x rows in y tables.

I'm not even sure, whether I'd use the first letter of the types. We normally have numerical keys for categories or types and store the corresponding descriptions in a seperate table. When the description changes, you don't have to remember that the first letter and the description don't match anymore. And what if you have two descriptions starting with the same letter?

One further advantadge of having a separate description table is internationalization: You can extend the description table to have an additional column for every language or (IMHO better) to have on row for every language including the language code.

hth,
Heinz Received on Thu Nov 08 2001 - 08:41:39 CET