Mysql – Database structure for multiple users sharing one dataset, but also many users with their own dataset

MySQLusers

I'm building a web app with php/mysql, and I'm trying to figure out how to set up multiple users sharing data. Here's the best analogy I can come up with on what I'm trying to do:

You have a calendar app, and a user wants to allow his/her spouse to access the events on the calendar and update them as they see fit. There would be no difference in roles and both users would log in with their own credentials. You also have other users who may or may not have a significant other to share a calendar with.

Right now I have a users table with a user_id_PK field that contains the username & hashed password of the user. Its functioning fine in and of itself, but I want to add the functionality detailed above to allow multiple users to access the same data. So one user might send out some kind of automated email link to have the other person register for an account, and something about that link would tie the 2 accounts together. Any ideas???

Best Answer

One option to do it is having a many-to-many table, which will hold a calendar id and a user id who can see it. A calendar belonging to two spouses will have two rows in that table.