Hooking users from one project to the users table

[concept_core]

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 :

[r937]

@concept_core

How would I display this back in visuals ?

that diagram looks fine to me

[concept_core]

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

[r937]

@concept_core

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

[concept_core]

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

[r937]

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

[concept_core]

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.

[r937]

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 */ );