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?

Answer 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