How to limit inserts to one row per user per month?

Question

I would like to make a monthly contest. I have Users and Products. Each user can vote for products he/she likes the most this month. My vote table looks like this:

+---------+-----------+------------+
| user    | product   | date       |
+---------+-----------+------------+
| 1       | 1         | 2018-09-03 |
| 1       | 2         | 2018-09-03 |
| 1       | 2         | 2018-08-28 |
+---------+-----------+------------+

A user can like the same product but not in the same month.

My problem is that I can't figure out a valid Primary Key to prevent duplicate votes.

If I use PK (user,product), a user cannot vote more than one time for the same product. If I use PK (user,product,date), a user can vote the same product once a day.

How can I prevent duplicate votes within the same month?

Note: date should be a valid DateTime column.

Answer 1

MySQL does not allow functions in keys, only column references. So if you want to restrict by a UNIQUE key, you need another column that would contain only the year and month for the event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you are using MySQL 5.7 or newer, you can declare that column as generated and define an expression converting the day part to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX permonth (user, product, rdate). This is the uniqueness established on the physical level.

On the logical level uniqueness can be achieved by (at least) two common ways.

First, you can define a BEFORE INSERT trigger that would perform extended comparison of the dates and decline to insert a duplicate. But triggers are not a very clean and easy tool because of their implicitness, and my advise is to avoid triggers if possible.

The more robust way is to wrap the insert statement into a stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ....