CODEWITHSUNDEEP
database-designdrop-down-menu
0x4B1D
0x4B1D

Reputation: 933

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.

Upvotes: 5

Views: 3894

Answers (2)

Jim Jimson
Jim Jimson

Reputation: 2528

From a reporting point of view #2 makes things a ton more difficult, especially if you're using tools that hit the database directly, like SSRS or Tableau. You'll need to put in stored procedures or views to make reporting possible. The issue is that joining to one table a number of times can be difficult in a lot of reporting tools.

Yes, I totally understand that adding in a new table every time you want a new dropdown is a pain in the backside when you're developing an application, but by going with option one, you're going to save the backside of a poor BI developer who's getting yelled at by the CEO.

Upvotes: 3

Jordan
Jordan

Reputation: 2758

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.

Upvotes: 3

Related Questions