Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list.
In other words, the developer sets the list of items. The user must either select an item or type a value.
Examples:
- How did you hear about us? {Web, TV, Friend, Radio, other}
- Reason for contacting us? {Sales, Tech Support, Customer Service, other}
- Select your printer model?
Proposed schema:
model: model_id (PK), model_txt (UK), show (bool)
request: request_id (PK), model_id (FK), {other non-relevant fields}
Display models where show = 1
On insert,
- If model selected from list, use its model_id
- If model typed,
- If model_txt exists, use existing model_id
- Else, insert new model_txt and set show = 0
Likes:
- This solution avoids NULL values and empty strings
- Obtaining
model_txt
is the same for all requests regardless of if the user typed the entry.
Dislikes:
- The
model
table will be polluted with items only tied to one request. - The insert model_txt requires an extra step before the INSERT command.
How can I improve this schema?