Should I use one table or many for drop downs? 1

[LonnieJohnson]

This may be more of a database question but wanted to know what Developers actually thought.

Is it better to have multiple tables for storing values used in application drop downs and list boxes or putting them in a single table?

This wouldn't be for complicated drop downs, just ones that had a single column drop down.

So say I wanted to put values for Gender, Race, Hospital Names and Department Number all in one table. Even though they are not related wouldn't it be easier to have them all in one table?

Any and all professional advice would be helpful.

Thanks.


ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[jmeckley]

the traditional approach is to have a table per lookup type. for most systems this works and doesn't cause too much hassle.

a global lookup table works the application allows clients to extend the functionality of the application. if a user adds a new field to an entity and they want to predefine lookup values they are stored in a global lookup with a discriminator.

there is another approach to consider. if a lookup is just a lookup, why do you need a table at all? Gender only has 2 options Male and Femail. create an Enum and store the text value in the database. this reduces the number of joins and tables in the database.
for lookups like Race, there are too many to list to be all inclusive. by creating an open ended auto complete you can provide the user with predefined values to select from, if they enter an non-existent value you can prompt them to confirm the entry is correct. the predefined values at queried from the database as a distinct list of values similar to the user input.

depending on the type of data this may or may not be an option.

still another approach is to store lookups in a document database or key/value store. these types of persistent storage mechanisms provide alternative advantages over the traditional RDBMS. Lately this has been referred to as 'nosql'. google that term and you will find out more on the subject. Ayende has a good series of posts on the topic and various examples of alternative "databases" and the type of problems they were designed to solve.

I don't see how Hospital Name or Department would be lookups. I would consider them to be domain entities with unique attributes and behavior.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[LonnieJohnson]

Wow. Thanks. Those are more options than I expected.

I appreciate all and any input.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!