Re: composite foreign key one field constant?

On 21 Nov 2002, ddwoske_at_yahoo.com wrote:
> There are differing opinions on this point, and at the moment,
> I just don't see how it could create complicated and buggy sql
> down the road.
>
> To me having one value table to maintain is easier than 20. I
> would be curious to read any articles or design patterns for
> this type of thing, but simply didn't find any.

Suppose you to types of things to keep track of.

account_status 1,0
customer_gender M,F

You have the type table:

type_id Lookup_string Small String lookup_type

------- --------------  -----------     ------------
1       Good            1               account status
2       Bad             0               account status
3       Male            M               customer gender
4       Female          F               customer gender

We have two tables, account and customer but I only need to show one to see the flaw:

Account
acct_id acct_status
------- -----------

1       1
2       2
3       3
4       4

This will be a legal set of data with the "one-lookup design". The child relationship from the acct_status column to the type_id in the main lookup table can never guarantee that acct_status values are _ACTUALLY_ acct_status values. The only thing it can guarantee is that the lookup exists in that one table.

-- 
Galen Boyer