storing drop-down values, what is a good approach

问题:

Imagine you have a website with several drop-downs that are populated from the back-end database. The objective is to store these values in the database and retrieve them whenever you render the form.

I have seen two approaches used:

1) One table per list type:

profession_type
|id|value|

hobby_type
|id|value|

2) One table for all look-up values:

|id|type           |value|
|0 |profession_type|value|
|1 |profession_type|value|
|2 |profession_type|value|
|3 |hobby_type     |value|
|4 |hobby_type     |value|
|5 |hobby_type     |value|

Are there objective advantages to either of it. #2 seems to be more generic (you do a select from table by type to populate a specific drop-down), but the table will tend to be significantly larger than if you were to use #1. Also, if you use #2, all of the foreign keys are pointing to the same giant table. It does not seem like a big deal, but in my eyes this approach looks more convoluted.

回答1:

I'd go with #2. You can retrieve and cache all info and even if you add new types you needn't change your database or your retrieval logic.

If you are adding and changing tables you need to touch your database schema for all changes in the future and change retrieval code for the new tables.

Furthermore your concerns for the table size are PROBABLY unfounded. Unless you have millions of rows it'll be fine. I'm imagining some hundreds OR thousands of rows? Databases can easily handle that.