I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists.

I have 3 tables, one for activities where it has an auto incrementing primary key called 'activityID' and two tables for each list. Users are not stored in the MySQL DB for several reasons but have a GUID to identity them.

I only need to make 2 fairly simply select queries per list table:

  1. Select/return all activity id's for a specific user in a list

  2. Select/return all activity rows for a specific user after joining the activity table and whichever corresponding list table.

I've researched on stack exchange and elsewhere but have found conflicting answers on the following:

  • Should I use a composite primary key of the userID + activityID for the list tables, or just use an auto incrementing int as the primary key?

  • In either case, should I also have activityID as a foreign key to the activity table for each list table? (My understanding is I should to increase data integrity, and to take advantage of cascading delete if needed in the future)

Any suggestions for this is greatly appreciated, I know these things can be difficult to change once the DB is live. Thanks so much!

up vote 1 down vote accepted

As Kumar said, using a composit primary key will prevent you from having two entries with the same userID and activityID - you have to decide if this is the desirable configuration. Logically, I would say that a user can complete the same activity twice, so the "completed" list would have two entries, both with the same userID and activityID. In this case you would need a seperate primary key.

Generally I would recommend using the activityID as a foreign key. If you configure ON DELETE CASCADE the deletion of an activity will also delete all entries in both lists that refer to this activity. However I would rather recommend using ON DELETE RESTRICT which would prohibit the deletion of an activity as long as there is a corresponding entry in either list (this will prevent you from having invalid lists where an activityID cannot be resolved).

  • Would you say it's acceptable, for a 'count' column to be incremented upon a duplicate entry being added to the completed table? Better to do that than have duplicate entries i assume? – OhNoNotALinkerError Apr 6 at 9:02
  • It certainly is a posibility, but when an activity is marked as done, your code would have to check if there is already an existing activity for the user in the "complete" table. You might also be interested in the date/time it was completed, which would be easier if you have a seperate entry for each completed activity - you would just have to add a datetime field. If performance is not an issue (the rowcount is not in the millions) I would personally prefer duplicate rows - you can still get the count per activity with a simple query. – Flourid Apr 6 at 10:54

You may get your required output by activityID as a foreign key with auto incremented primary key. But there is possibility of duplicate entry in this case. So this is the best option to use composite primary key of the userID + activityID for the list tables.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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