1

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.

1

In my opinion, these restrictions are best maintained within the UI for said survey/questionnaire because a survey is designed to be situation-specific and highly volatile whereas a data model should be built to be robust and flexible, but not so rigid it become grossly limited in functionality. Regarding the rest of the answer though, these features primarily pertain to Microsoft SQL Server. Most of this functionality can be found in other RDBMS platforms, but the links I'll include are specific to MSSQL.

If you must push this sort of logic down to the database, you have a few options. The first one you've already touched on is the Unique Constraint. This is a quick check to execute, but it is applied to the entire table, so in your example if another "Chuck" comes and takes the survey his selection of Jogging on Monday (while a valid decision because it's a different Chuck) will throw a constraint error. Obviously the table would need to be designed so it could track unique users, etc. but hopefully you get where I'm going with the limitations of Unique Constraints.

The sibling of the Unique Constraint is the Check Constraint. These can be a little more fleshed out as you can create User Defined Functions wherein nested logic can be coded. So for scenarios B and C above, you could conceptually code up some logic to check for those conditions. Check Constraints with Scalar UDFs are often horribly slow and will cause performance issues, so this isn't an approach I'd ever recommend... but you can do it.

Another option within the database to handle these situations are Triggers, and in this situation specifically, DML Triggers. These can be designed to be even more complex than Check Constraints and given the right situation can perform relatively well. Again, these can also fall prey to poor performance if they're not written well, but this is another way to force logic down to the data model layer.

Hopefully this helps, but forcing this much business logic into the data layer is often frowned upon unless there is some sort of performance benefit, which in this case is unlikely.

  • Thanks @John. The participant names are for an illustrative purpose, they will be proper unique IDs. I am still learning at the moment. I am intrigued why business logic in data layer is discouraged, another exercise for me to find out. – Green Apr 19 '17 at 5:41
0

Data_Model_171293

You could design two tables like this

  • Participant_ID is the primary key in Participants table
  • Participants and Active_days are related via Foreign Key on Participant_ID column (One to many relationship)
  • Activity column should be defined as Not Null
  • Activity column can take only these values (Yes, None, RK, DK)
  • Day column in Active_Days table can have only Mon, Tue, Wed, Thu, Fri, Sat, or Sun
  • In case the record in Participants table has Activity value as 'Yes', you will have entries for that participant in Active_Days table (One record per day). Otherwise, you will not find any records in Active_Days table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.