Database Modelling

Problem Statement

How would you model to check if a user has accepted your “Terms and Conditions” or “COPPA” when building your website? Lets talk how would you track in your data store (MySQL or Postgres or Mongo DB).

The front-end is represented by a checkbox or click of a button ‘I Accept’, this translates to either a true or false and gets stored into the a column in the users table in your database which is a boolean field.

The Implicit Requirement

Sounds fair. One thing you may have missed is that all websites like Microsoft, Github, Google, Dropbox tend to update their Policies. They usually send out an email and give the users time to review and accept theT&C which is usually applicable from a particular date. Now this is a requirement that is generally missed in a fine print since the stories create miss this for various reasons.

Respecting the Schema

If your Product Owner mentions this has to be done, you have updated your T&C, you want to be a good person and want to notify your users on the next sign on or not continue using the services or use older code base or mechanisms so that there is no service interruption. If you had a boolean field, this is one may tend to do.

1. Run a cron to have everyone ‘UnAccept’ the T&C and update the new ‘T&C’ data and try to log out users
2. If you have a notifications module, you send a notification to the customers
3. If you have a banner module, you may add it to the banner where you usually mention announcements or outage information

Banners and Notifications should be presented to only those who did not accept. but its hard to track this information. Since once you updated the column information, you lost precious information about the user. Also,

1. We don’t want to show the banners for those who have accepted.
2. Some people may have accepted from their Profile page
3. You need to group users by who have not accepted the T&C versions (when you tend to modify them often and may have different settings for them instead of stopping to service them)

Elegant Schema Solution

The solution from this set of requirements would be to modify the boolean column in the database to use a CHAR or an integer (positive number) column with a NULLable or with a default value like 19700101 as the original date or a sequence of generated version numbers.

Every time a new T&C is updated, your code has the ability to compare the current version of the T&C with the one user has last read and accepted.

Other Advantages

This is good for analytical purposes as well and would allow user to accept T&C before hand itself instead of waiting till the switch is flipped after a deployment.

When you think from the User Experience point of view, it would be a blocking experience for the user to navigate and read the T&C and understand or discuss and accept. Notifying the user early and having the user accept early on would help them not worry on the day of your deployment.

Also, this approach helps your content/legal team deal with new T&C without developer intervention and unrelated to deployments.

One thing to note is that the T&C versions generated every time are an incremental sequence so that you can compare them.

This is a general advice for the type of data which should be accepted but the master reference can change over time.

Let me know what you think.