I am starting a C# Winforms application where 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.
