Need ideas

Question

I have a user table and an organization table in mysql.
User can belong to multiple organizations.
What is the best way to save the relationship between the user and the organizations?

I've thought of saving the row ids using the text field in both organization and the user table.
For example

Users:
id: 1
username: Toni
email: [e-mail]
password: xxxxx
organizations: "1, 2, 3, 4"

organizations:
id: 1
name: sololearn
bio: asd
users: "1, 2, 3, 4"

Do anyone have a better idea?

Answer 1

You probably need a connector table with `user_id` and `organization_id` columns. Each these column should reference the respective `id` column in table 'users' and 'organizations'

user_id | organization_id
1 1
1 2
1 3
1 4

This may seem like a waste, but I guess it might help to enforce data integrity.

Answer 2

What about assigning different organization specific permissions to each user?