CODEWITHSUNDEEP
sqlpostgresqldatabase-designforeign-keyscreate-table
PanchoChi
PanchoChi

Reputation: 37

PostgreSQL creating foreign key to all rows of another Table

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

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?

Upvotes: 0

Views: 1377

Answers (2)

Conffusion
Conffusion

Reputation: 4485

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).

Upvotes: 2

GMB
GMB

Reputation: 222702

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

Upvotes: 1

Related Questions