A fictional case: a participant is asked which days of the week s/he goes jogging or swimming. The answer can be Mon, Tue, Wed, Thu, Fri, Sat, Sun as well as None, RF (Refuse to answer), DK (Don't know). A participant can choose any day or a combination of them. But if a participant chooses None, RF or DK, other options cannot be selected. How do I model this in an table, ensuring data integrity.
An example:
+-------------+----------+------+
| Participant | Type | Day |
+-------------+----------+------+
| Alice | Jogging | Mon |
| Alice | Jogging | Tue |
| Alice | Swimming | Wed |
| Alice | Swimming | Thu |
| Bob | Jogging | Tue |
| Bob | Jogging | Wed |
| Bob | Swimming | Tue |
| Bob | Swimming | Wed |
| Bod | Swimming | Sun |
| Chuck | Jogging | Mon | <-- A*
| Chuck | Jogging | Mon | <-- A*
| Chuck | Jogging | Tue |
| Chuck | Jogging | Sun |
| Chuck | Swimming | Sun | <-- B*
| Chuck | Swimming | None | <-- B*
| Dan | Jogging | Mon | <-- C*
| Dan | Jogging | RF | <-- C*
+-------------+----------+------+
A* - A unique constraint on (Participant, Type, Day)
can prevent this duplication.
B*, C* - How can these be prevented if a participant choose None or RF, no other options can be entered.
Another example would be diet choices. Options include: low fat, low carb, gluten free, vegan, seafood, etc. A combination of low fat, low carb, gluten free is possible. But a combination of vegan + seafood is not possible.