I've a credit card applicatoin with numerous "pick list" organizations (accustomed to populate single choice dropdown selection boxes). These organizations have to be drawn in the database. How do you persist these organizations within the database? Must I produce a new table for every pick list? It is possible to better solution?

Previously I have produced a table which has the Title from the list and also the acceptable values, then queried it to show their email list. I additionally incorporate a underlying value, so that you can return a presentation value for that list, along with a bound value that might be much uglier (a little int for stabilized data, for example)

CREATE TABLE PickList(
    ListName varchar(15),
    Value varchar(15),
    Display varchar(15),
    Primary Key (ListName, Display)
)

You might give a sortOrder area if you wish to by hand define an order to show them in.

It is dependent on something more important:

  • if they're immutable and non relational (think "names people States") a disagreement might be made they shouldn't be within the database whatsoever: in the end they're simply formatting of something simpler (such as the two character code designated). It has the additional advantage you don't require a round visit to the db to fetch something which never changes to be able to populate the combo box.

    After that you can make use of an Enum in code along with a constraint within the DB. Just in case of localized display, so you'll need a different formatting for every culture, you'll be able to use XML files or any other assets to keep the literals.

  • if they're relational (think "states - capitals") I'm not so convinced in either case... but recently I have used XML files, database constraints and javascript to populate. It really works very well and it is easy around the DB.

  • if they're not read-only but rarely change (i.e. typically can't be transformed through the consumer only by some editor or daily batch), i quickly would consider the chance of not storing them within the DB... it might rely on the specific situation.

  • in some cases, storing within the DB may be the way (think about the tags of StackOverflow... they're "research" but may also be transformed through the consumer) -- possibly with a few caching as needed. It takes some careful securing, however it works good enough.

Well, you could do this something similar to this:

PickListContent

IdList  IdPick  Text  
1       1       Apples
1       2       Oranges
1       3       Pears
2       1       Dogs
2       2       Cats

and optionally..

PickList

Id    Description
1     Fruit
2     Pets

Create one table for lists and something table for list_options.

 # Put in the name of the list
 insert into lists (id, name) values (1, "Country in North America");

 # Put in the values of the list
 insert into list_options (id, list_id, value_text) values
   (1, 1, "Canada"),
   (2, 1, "United States of America"),
   (3, 1, "Mexico");