Schema For “Other – Add Comment”

best practicesdatabase-designdenormalizationnormalization

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,

  1. If model selected from list, use its model_id
  2. If model typed,
    • If model_txt exists, use existing model_id
    • Else, insert new model_txt and set show = 0

Likes:

  1. This solution avoids NULL values and empty strings
  2. Obtaining model_txt is the same for all requests regardless of if the user typed the entry.

Dislikes:

  1. The model table will be polluted with items only tied to one request.
  2. The insert model_txt requires an extra step before the INSERT command.

How can I improve this schema?

Best Answer

Nice question. My objection to your design is: you choose to consider user input and predefined input as the same thing (the only difference being the value of show). But they are not, in my opinion. Because predefined input are different options, but different user inputs could indicate the same idea written with synonyms, wrong spellings, etc, or could be just jokes or junk characters.

You say that this saves you from having empty strings or NULLs. But I believe that the absence of a value (custom text) is a valid use for an empty string.

Related Question