Pulldown menu with data from 2 tables or 1 with an ENUM choice?

[ljjtek]

Not sure the best way to go about what I want to do so I'll start with just trying to explain what I ultimatley am trying to do. I would like to have a database in which people enter certain information about a univerisity of their choice.... Rather than have a field which has every possible university listed (which would be a lot of names, many of which would never be chosen) I'd like to have a pulldown list that holds names of perhaps the more popular universities which I would initially put in, and then if the university is not there, they would have the option of adding it and this would then be added to the preset list of names. I'm not sure if I should use 2 tables, one which would hold the main data and one that would just hold the university names? Or would I have 1 table with an ENUM field which would have the possible choices...? (this would also have to allow for NEW choices to be added though).

 

[sleipnir214]

ENUM is a very poor choice for a field of this type. In nearly every situation where you have a choice of preexisting data which may be updated from time to time, a related table is a better way to store the possible values.

Also, allowing your users to add entries to the choices table is a bad idea, too. You will find that the table will be a collection point for every misspelling variation on university you've ever seen.

I might present the choices much the same way a school is chosen at

http://www.classmates.com

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ingresman]

You could use an auxillary table of less popular choices or store all the names in the single table with an indicator if it's popular or not and then select from the table where popularflag="y" where appropriate