Composite vs auto incrementing integer as Primary key

Question

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!

Answer 1

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).

Answer 2

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.