CODEWITHSUNDEEP
postgresqldatabase-designschemarelational-database
tg3
tg3

Reputation: 1

How to model 3 tables with multiple many-to-one relationships?

Companies :

Company ID Company Name
1 Google
2 Facebook

Teams :

Team ID Team Name
1 Video Performance
2 Storage Optimization

Employees :

Employee ID Employee Name
1 John Smith
2 Tracy Johnson

The following relationships exist:

These tables would be joined such that all information for employees (and the team and company they are a part of) can be joined with another table referencing their ID. What would be the optimal way to design this schema in PostgreSQL?

My team is back and forth between different designs:

  1. Having the foreign keys directly in the tables (employees has fk to companies and teams, teams has fk to companies).
  2. Having an intermediate table representing Employee Relationships to store the primary keys of all three tables.
  3. Having an intermediate table for each relationship (employee-company relation and team-company relation and employee-team relation).

Which of these is the most sustainable as application needs grow?

Upvotes: 0

Views: 228

Answers (1)

Nic3500
Nic3500

Reputation: 8621

The main point here is "many to one". Usually for that you would put a foreign key in the many table since there is only one possibility.

So you would have:

Company
    companyid, PK
    
Team
    teamid, PK
    companyid, FK, Company.companyid
    
Employee
    employeeid, PK
    companyid, FK, Company.companyid
    teamid, FK, Team.teamid

That poses a potential problem though. Is it possible that an employee is in team1 and company1, even though team1 is not in company1?

What I mean:

Company
    1
    
Team
    1   NULL    <-- record "1 1" does not exist, team1 is NOT linked to company1
    
Employee
    1   1   1

From the Employee table, we might assume that since he is in team1 and company1, it follows that team1 is related to company1.

That can only be answered by checking your "business" logic. If it is impossible to have such a scenario, you could then remove the foreign key companyid from Team, since an employee in team1 - company1 would imply that team1 is linked to company1.

That covers design 1. For designs 2 and 3, you do not need link tables, since that is for many-to-many relationships. And the problem I have posed here would still apply anyway. Only you - within the context of your business - can choose here.

Upvotes: 0

Related Questions