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