database-designMySQL
How to store "other" text?
In summary, I have too many of these kinds of fields (like hundreds?):
http://i.stack.imgur.com/0H81O.png
I don't know an appropriate way to handle this.
If I store only text of these options, then I will have data redundancy and no foreign key. Maybe foreign key does not matter since I am allowing users to store "other" anyway.
You want to have something like this:
This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)
Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:
UOM Conversion
UOM From UOM To Cal Step Operator Factor Constant
Kilograms Pounds 1 * 2.2
Celsius Fahrenheit 1 * 1.8
Celsius Fahrenheit 2 + 32
This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.
That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.
Best Answer
If it was a radio list where only one option could be specified then I'd use one column and store the selected value if something other than "other" were picked and the value from the free-text box otherwise.
As it appears that your application allows selecting multiple item (which may or may not include "other") then you need to store a list of possible options per form. A boolean per check box may be inefficient, but do avoid a comma separated list (i.e. a field called "foods" in which you store "rice,soup" in the example you give) as that can make reporting (amongst other things) difficult.
How I would go about storing this data would depend on the amount on the rest of the data set: there are a couple of methods that are optimal for different circumstances. Could you update the question to give more details about the application and its data?