I have two tables - "Users" and "Projects", i want to be able to show which users are assigned to which project. There may be multiple users assigned to the project.

I was thinking of creating a 'project_users_matrix' table where a new column would be created for each user and a new row created for each project, then the cells can just show a 1 or 0 depending on if the person is working on that project.

The 'cleaner' option would be to have columns 'user_1', 'user_2', 'user_3' in the project database but then there can't be an indeterminate number of users for a project.

Is there a better way to do this? It seems like there should be...

CodePudding user response：

If users can participate in many projects, and projects can have many users then you have a many-to-many relationship and you need three tables: users, projects and an association table that contains user ids and projects ids only. Each active user-project combination should have a row in the association table.

If users cannot participate in multiple projects simultaneously then you have either a one-to-many relationship between projects and users, or users and projects, which can be expressed by a foreign key column on the many side.

CodePudding user response：

you need to create 2 more fields in project-table 1st for User_id and 2nd for Active/inactive in 1st field you need to store id of user who is working with that project and in 2nd field enter value 0/1 and provide button that if user is active on that table it shows 1. and once it done with his work.user can update it with 0.