ASP.NET MVC 2 View Model DDL lists: Where to store the choices, hard code in a view or store in a database?

Question

We currently have a "Lookup" table which contains a set of possible choices for things like a drop down list. If this were a list of state abbreviations, LookupID would represent the set, and LookupItemID would represent the individual state.

customerID   int     PK
lookupID     int     PK
lookupItemID int     PK
lookupValue  string

This addresses storing DDL options, but not how they should end up in the View. It seems insane to setup referential integrity between every table that has an option list. For example, we have a "Buyer List" that has a list of Buyer Names, used in the Product screen (certain Buyers handle certain products)

Is there some way get the option list to the View? How about how to know which list is used for which field? Storing the field name in the table sounds like an EAV solution, which we want to avoid. However, we need to store the options for a field somewhere.

Any suggestions?

Answer 1

I have generally seen this handled in a couple ways each with pros and cons. 1 way would be to create a table for each and every lookup. This helps to reduce redundancy on the database side but adds a lot more joins on the reporting side. I have also seen scenarios where creating one Option table where any time that you have a a drop down the values for the drop down are stored in the option table. If you use an Option Table then you will want an OptionGroup column that is used to denote a group of Options (as opposed to a field as a Group may be re-used for multiple fields). The advantages for using one table as opposed to many are a reduced number of tables to administer.

If the options in any of the DropDowns are a very set list such as Active/Inactive I have also seen the use of an Enum on the code side used in logic as User.status == Status.Active is friendlier than User.status == 5....

The Use of just one or multiple tables really depends on the rest of the architecture of the system but for smaller sites I am finding I prefer one table as it is easier to create one admin ui.