>>>>Actually if your master lookup table has a good primary key, you use that for your RI and you are all set.
>>>>
>>>>I use a single table for all picklists on all of my systems.
>>>>
>>>
>>>How would you set it up then to only connect main table with right entities?
>>
>>Picklists
>>
>>pk int (identity) (primary key)
>>Picklist_Name varcar(50)
>>Picklist_Display varchar(50)
>>Picklist_Display_Order smallint
>>
>>
>>
>>Data Table
>>field1
>>field2
>>field3
>>Picklist_code int (fk is pk on Picklists table)
>
>When you need to show available choices for something, do you select from Picklists based on Picklists_Name?

Yes. Actually I have a table of picklist names with a primary key - I store the primary key for the name in the picklist definition table (same basic idea).


>
>Also, you can insert into your data table a valid Picklist ID, but it would be for completely different purpose - there is no programmatic way to prevent it except for the trigger, IMHO.

Yes - if you go under the hood you can scramble data, but in the applications, picklists are only the proper keys for the particular picklist name. I use a stored procedure to pull the picklists - ie Get_Picklist('thispicklist') returns a table of valid choices for ThispickList in the order specified by PickList_Display_Order.