Re: Simple question on data design...

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

As a compromise between the text value and the lookup table/foreign key approach, you could also try short character strings, eg

addresstype char(4)
check addresstype in ('PRIM','BILL','SHIP', 'LOCN' )

The lookup table approach, eg

addresstype integer references addresstypes

uses the same amount of storage for 32-bit integers. Granted you could shorten the integer a bit, but the point is that you don't lose much by shortening the natural-language-based codes.

The other "weakness" of the lookup table is extensibility. If you want the system to allow people to enter more address types on their own, the table approach is best. However, if crucial parts of the system are already hard-coded for four values, then you're OK making it clumsy or impossible for users to add more. The worst alternative is letting people enter arbitrary types without any logic to handle them. Received on Fri Nov 09 2001 - 22:25:57 CET