Sergey,
Thanks. That's what I thought. I guess I will just go back to seven tables then. As I said, I initially started with seven tables and for some reason, which eludes me a the moment, I decided to go with one "metadata" table.
I may post another question in the near future if I come upon some issue using seven tables again.
Thanks again.
Elgin
>You have to use 7 separate tables to take advantage of foreing keys.
>
>>I am starting a C# Winforms application. Part of the main data entry form consists of seven comboboxes allowing the user to select a single item from each combobox, or leave the column blank if they so choose. I am representing the choices in the comboboxes as text, of course, but in my main table I will store the unique ID (int) that they choose. I want the user to be able to maintain the lists that populate the seven comboboxes.
>>
>>What is the best database design for doing this?
>>
>>I have tried two different methodologies so far. First I created seven tables, one for each combobox, since each one has a different meta data type. The tables had three columns - ID, Name, and Description. The ID was the primary key that I related to the main table.
>>
>>Then I thought it would be better to combine all seven lists into one "meta data" table by simply adding a TYPEID column to the table. That made designing the form to maintain the meta data lists a bit easier. However, when I try to relate my "metadata" table to my main table ON MULTIPLE COLUMNS AND enforce referential integrity I get the following error in the SQL Designer and the VS Dataset Designer:
>>
>>" Introducing FOREIGN KEY constraint 'FK_docs_status' on table 'docs' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. "
>>
>>I am not allowing the user to modify the uique ID in the metadata table. However, when the user deletes one of the metadata "status" items, I want to "Set Null" the status column in my docs table.
>>
>>What is the best design pattern? Should I maintain seven different "metadata" tables?
>>
>>Thank you.
>>
>>Elgin Rogers