I need to allow a user to select a value from a few available options, and to store his selection in the "users" table. So far so easy. My problem is that I need to allow him also to select "other", and then he enters free text, that also should be stored. How should I design the tables and the querys to enable this?
Thanks a lot!
Create a table ("Options" or whatever) that contains a key, and a character column. Populate the table with the allowable values, including "Other".
Create a table ("Users") that contains (in addition to keys and other columns of interest) a number (not nullable, foreign key to the "Options" table) and a character column (nullable) to hold any typed text.
In your program (not your database design) populate a combo box that allows him to select values. Also in your program, write some code that tests the selected value from the combo box and get the key value from the "Options" table, and display another window/dialog to solicit some text for "Other" if necessary.
Store in your "Users" table the key of the option selected (including "Other") and the text if he typed any...null otherwise. Easy as pie.