I have two different databases. One of these is a Users table, and another is a Groups table.
A group can have multiple users, and a user can have multiple groups
What's the best way to associate a user with a group? I see some options:
- Store a list of users in the Group and store no list of groups for the user.
- Store a list of groups in the User and store no list of users for the group.
- Store both a list of users for each Group, as well as a list of groups for each user.
What are the pros and cons of each design? I think the 3rd option is the worst as it requires synchronization work, and duplicates data.