PostgreSQL creating foreign key to all rows of another Table

Question

I am working on creation of a PostgreSQL Database.
I have 2 tables - Users and Projects

table Users consist of: Id, Username, Password, and Project_ID (not sure if it's needed)
- Primary key - Id
- Foreign Key - Project_id as ID in Projects Table
table Project: Id, Name, Status, Deadline

How can I (if I can) implement a relation one-to-many in this case?

So if I make a JOIN query to show me User with certain ID and all his linked Projects?

Or It would be easier to create a User_ID foreign key in the Project table relating to Users table ID?

Conffusion · Accepted Answer · 2020-09-17 11:39:24Z

2

If your users are just project members, a single project may consist of multiple users and a user may be member in multiple projects. In that case @GMB provided the right answer.

But the correct answer depends also on your business: if every project has exactly one manager you can define a manager_id in project and this way every project is linked to 1 manager and a manager (=user) can be manager of multiple projects.

In the user_project you can also specify the role of the user within the project (assuming a user has only one role within a project).

answered Sep 17, 2020 at 11:39

Conffusion

4,5152 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PanchoChi Over a year ago

Thanks, I really understood that didn't specify in particular what I want to have. I want to have User with all his projects, had to ask my self first what I want =D

GMB · Accepted Answer · 2020-09-17 11:24:07Z

1

It really looks like you have a many-to-many relationship, where each user may participate multiple projects, and each project may involve multiple users. If so, then you need a third table in your design to represent that relationship - this is called a bridge table, or a junction table.

Sample DDL:

create table users (
    id int primary key,
    username text
);

create table projects (
    id int primary key,
    name text,
    status text,
    deadline date
);

create table user_projects (
    id int primary key,
    user_id int references users(id),
    project_id int references projects(id),
    unique (user_id, project_id)
);

Now say you want to list all users users, and all the projects they participate:

select u.*, p.*
from users u
inner join user_projects up on up.user_id = u.id
inner join projects p on p.id = up.project_id
order by u.id, p.id

answered Sep 17, 2020 at 11:24

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

PanchoChi Over a year ago

Thanks for the answer. I probably didn't express myself correctly, that I want only to show the User with all his Projects, but not vice versa. Anyway I got the idea :)

Collectives™ on Stack Overflow

PostgreSQL creating foreign key to all rows of another Table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related