# Select lists - DB best practice **URL:** \ **Category:** Rails\ **Created:** [December 20, 2006, 2:33pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894 "2006-12-20T14:33:50Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 2:33pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/1 "2006-12-20T14:33:50Z")
Hey guys, I’m putting together an app with user profiles, and each user will be able to update a section called ‘quick stats’. This will involve lots of drop down lists with options like ‘hair colour’, ‘height’, ‘Location’ etc etc. The way I m thinking about doing it is to create new table for each group of options like… * * * hair\_colour id colour * * * and then in the profile table have… * * * profiles id user\_id hair\_colour\_id * * * It just semms like there must be a better way of doing this without creating so many tables? ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 4:07pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/2 "2006-12-20T16:07:07Z")
The way you are doing it IS best practise. These types of tables are commonly called “codes” or “lookup” tables as they are just code/description pairs. I usually prefix them with LU\_ to distinguish them from the main application tables. The other way to do it would be to have a single table for all codes by adding a “type” column that would differentiate the type of code but I would advise against it. -Paul ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 7:14pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/3 "2006-12-20T19:14:21Z")
Paul: Could you please expand on why it’s a best practice to use separate lookup tables? Also, why do you advise against using a single table? I’ve used a single table in the past and want to know why it’s sub- optimal. Thanks, -Anthony ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 7:16pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/4 "2006-12-20T19:16:29Z")
Anthony, Normalization is the short answer. Lets say you need to add an effective\_date column to one of your lookup tables because the code is only effective starting on 1/1/2007 for instance. If everything is lumped in one table all of your codes would have to take on that new attribute. This might not seem like a big deal but now your table encapuslates two different data models. Eventually you end up with code that says “if type = this do that”. Yes, you could just create a new table at this point to model just the code that needs the effective\_date column and leave the existing table alone. But, that is why it’s best practise to start that way. However, what you have done in the past WILL work within the limitations just outlined. -Paul ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 9:46pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/5 "2006-12-20T21:46:01Z")
Paul, Thanks for the explanation-- and the long answer! I appreciate stuff like this because it makes it easier to choose the correct way the next time I’m confronted with choices. I have been using single table inheritance (STI) for my lookups. When something really different comes along-- you’re right-- I do have to change to a separate table. The trade-off is that I don’t have to repeat myself for the standard stuff: all my lookups have a display, key value, effective\_date range, and active flag. With many people throwing around DRY as a good idea, do you think that separate tables is always the best starting place? I think that for tables that have a low chance of being structurally changed, STI is a decent time saver. But, I’ve got to admit that my proverbial short cuts don’t always pay dividends. What’s your feeling on STI? Thanks, -Anthony ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 21, 2006, 12:04am UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/6 "2006-12-21T00:04:52Z")
Anthony, I have not used STI before but it seems rails provides support in the framework to overcome some of the negatives I mentioned.That is, it provides separate models for each entity as if they each had their own database table and yet stores all of the data in a single table. The main issue with it from my perspective would be that even though you can split it into separate models you cannot designate for instance that columns A and B are only to be used by model A and column C only with model B. However, for something like simple lookup tables it may have it’s place. If your successfully using it in that regard I would not change anything. -Paul ---
**Author:** ![daborg69](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/d9b06d/32.png) [@daborg69](https://www.ruby-forum.com/u/daborg69)\ **Post date:** [December 20, 2006, 10:16pm UTC](https://www.ruby-forum.com/t/select-lists-db-best-practice/79894/7 "2006-12-20T22:16:09Z")
What you are talking about is 3NF normalization. Its not always the “best” solution. Upside is that you reduce the chance of invalid data. The downside is that you start doing tons of joins in your queries which slows things down. I don’t even use STI. I use CONSTANTS to define select lists and make sure to comment what the constant is in the migration definition so others can look it up. You can avoid funky data input by performing validation on the data relative to the constant before saving with: validates\_inclusion\_of :hair\_color, :in =\> HAIR\_COLORS I am not a DBA so my way is not necessarily the best way. But it works and I haven’t ever had any data invalidity issues.