jeudi 6 août 2020

Is composite key as a foreign key a good practice in rails or any other ORM?

I am using ActiveRecord and Ruby on Rails. These are my entities: Organization, Projects and Team Leads.

Organization has_many Projects. Project has_many Team Leads. These are the tables so far:

organizations
 id
 <attributes>

projects
 id
 <attributes>

team_leads
 id
 <attributes>

organization_projects
 id
 organization_id (FK)
 project_id (FK)
 <attributes>

project_team_leads
 id
 project_id (FK)
 team_lead_id (FK)
 <attributes>

Now, the task is to add Contractors in this system. There will be certain attributes of contractors which will be at the project level and organisation level. Hence, the new table should look something like this:

project_contractors
 id
 project_id
 contractor_id
 <attributes>

organization_contractors
 id
 organization_id
 contractor_id
 <attributes>

Now, the actual contractors will be managed by a team lead. Hence there will be a table which looks something like this:

team_lead_contractors
 id
 team_lead_id
 contractor_id
 <attributes>

Now, the issue here is: how to ensure that the contractor and team lead which is entered in this table is belonging to the same project?


Option 1:
 team_lead_contractors
  team_lead_project_id (FK from team_lead_projects table)
  contractor_id (FK from contractors)

Option 2:
 team_lead_contractors
  team_lead_id (FK from team_leads)
  project_id (FK from projects)
  contractor_id (FK from contractors)

Option 3:
 team_lead_contractors
  team_lead_project_id (FK from team_lead_projects)
  contractor_project_id (FK from contractor_projects)

With Option 1 - there is no way to make sure that the contractor belongs to the same project for the team lead. Hence, data consistency needs to be maintained at the application level.

With Option 3 - Project is coming from both team_leads and contractors. In order to make sure that the project is the same, data consistency needs to be maintained at the application level.

With Option 2 - Data consistency can be maintained by having composite foreign keys. i.e. FK(team_lead_id, project_id) REFERENCES team_lead_projects(team_lead_id, project_id) and FK(contractor_id, project_id) REFERENCES contractor_projects(contractor_id, project_id). However, the issue with this one is: there is no built-in way to do this in Rails.

Is there any way to maintain data consistency? Also, it is worth it to override Rails and add a constraint at the database level for this particular scenario?

Aucun commentaire:

Enregistrer un commentaire