Hooking users from one project to the users table

Hello everyone,

I am trying to learn the basics of database design.
And I made projects table and a users table.

Now a project can have a max of four users + the project owner.
How would I display this back in visuals ?

This is what I have now :

that diagram looks fine to me

But it feels for me that is does not make sense for the collaborator_id * part.
is this how you would normally do this ?

me? depends on whether i was solid-as-a-rock sure that there would always be 4 collaborators, never 5

There will never be more than 4 collaborators and you have the author which is assigned using the author_id.

so what part of it “doesn’t make sense” to you?

Well it doesn’t feel right to have 4 fields aim to one id field on users.

I also talked to another developer and he explained that using one field with an json array would not be the best way to do it.

That’s why I want to know if this is the good way to do it.

one field with a json array is definitely worse…

your way is okay, but still not the best…

best is a separate table, called project_users, which has one row per contributor or author

CREATE TABLE project_users ( project_id INTEGER NOT NULL , CONSTRAINT project_users_project FOREIGN KEY ( project_id ) REFERENCES projects ( id ) , user_id INTEGER NOT NULL , CONSTRAINT project_users_user FOREIGN KEY ( user_id ) REFERENCES users ( id ) , PRIMARY KEY ( project_id , user_id ) , INDEX user_projects ( user_id , project_id ) , role CHAR(1) NOT NULL /* C = contributor A = author */ );

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.