Schema For "Other - Add Comment"

Question

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?

Answer 1

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.

Answer 2

There is no absolute right or wrong way to do it, because it depends on how you use this data afterwards, that is what kind of SELECT queries will you need on it.

So your schema could be good enough without anything to improve.

Here is my alternate take that could give you other ideas:

• model: model_id (PK), model_txt (UK)
• request: request_id (PK), model_id (FK), {other non-relevant fields}
• request_model_other: id (PK), request_id (FK), other_txt

In that way you do not "pollute" your model table with input by users. It has the same "likes" as your own (you will just need an extra LEFT OUTER JOIN request_model_other or similar) but not the dislikes. As the dislikes, it is another extra table, that has to be taken into account in your requests.

You could even "merge" the 2 tables with just:

request: request_id (PK), model_id (FK), model_other_txt, {other non-relevant fields} if you specify in a constraint that either model_id or model_other_txt are NOT NULL (but not both at the same time). You will then obviously need to accept having a lot of NULL values.

But again, I recommend you to think about how you will query this data afterwards to find the model that most suits your needs. Also how often the "other" case could happen? It is more like 1%? 10%? 90%? The design and optimisations would depend on this too...