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:
Select/return all activity id's for a specific user in a list
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!