Member Avatar for atal

Dear All,
I have a [project] table which includes Project name,Startdate,EndDate,Location.and [SubContractor] table which includes id,subContractor_name. each project has one SubContractor at time and one SubContractor may have many projects at time.

There are cases in which the first SubContractor leaves the project for some reasons and the company give the project to a second SubContractor.

My question is what would be the best way to Normalize or Design these tables. shall I have a middle table(Many-to-Many) or shall include these two fields in [Project] Table called SubCont1_ID,SubCont2_ID(both of them linked to the [SubContractor]table)
Please help me in this issue.

thank you in advance
the above question was answered by Robert but Now:

New Requirements has been added please clear me

The project is going to be issued to a Contractor and that Contractor is going to give the project to another Sub-Contractors there is going to be many subcontractors for one project and one subcontractor may have many projects.

Now how would i maintain the realtionship between Projects,Contractors and SubContractor, Is it going to be Projects-To-Contractor(Many-to-Many),and Projects-to-SubContractor(Many-to-many), Contractor-to-SubContrator(one-to-many) or would be different?

Thank you.

  • 4 Contributors
  • 3 Replies
  • 123 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by xyte

Dani AI

Generated

As , and already pointed out, the right move is to stop trying to shove multiple contractor IDs into the project row and to treat assignments as their own records. Two practical, durable patterns cover almost every business need.

Option 1 — contract-as-object
Create a contract record that ties a project to the prime contractor (one row per awarded contract). Then record each subcontract as a child record of that contract. Each subcontract row stores who, when, scope/value, status and audit metadata. This is best when you must track legal/financial relationships and preserve a clear chain (which prime awarded which subcontract).

Option 2 — role-based assignment table
Keep one canonical parties table (companies/people). Use a single assignment table that links a party to a project and includes the party role (prime or sub), effective-from and effective-to dates and an optional parent_party reference to indicate which party engaged the subcontractor. This is simpler and flexible when parties can act as prime or subcontractor on different projects.

Practical tips

  • Treat assignment rows as immutable history: insert a new row when someone is replaced, rather than overwriting. Capture who changed it and why.
  • Enforce referential integrity with InnoDB FKs and add indexes on (project, role, valid_from/valid_to) for queries.
  • Prevent undesired overlaps either in the app layer or with DB constraints/triggers if your DB supports them.
  • If you need to know who did which task, link tasks to the assignment (not directly to the project) so work is attributed to the responsible party.

Example query to get current subcontractors (adjust names to your schema):

SELECT p.name
FROM parties p
JOIN assignments a ON p.id = a.party_id
WHERE a.project_id = ?
  AND a.role = 'subcontractor'
  AND a.valid_from <= NOW()
  AND (a.valid_to IS NULL OR a.valid_to > NOW());

If contracts carry billing or legal terms, use the contract-as-object model; otherwise the role-based assignment table gives maximum flexibility while preserving history.

Recommended Answers

All 3 Replies

Member Avatar for Ramy Mahrous

why many-to-many, when some subContactor leaves the team, update it with the new one (ID) -- One-to-many.
if you want to keep track on the subContactors you should use many-to-many but keep in you mind to include period in the bridge table.

Member Avatar for Temmu

it is (almost) always a bad idea to have fields like item1, item2, item3... etc. as 1, it is not normal, and 2, you either have to few or too many columns.

ramymahrous above, offers a practical solution.

however, if you wish to keep track of who did what project, you'd have to have that 3rd table and safeguards in place to prevent inadvertent row deletion.

Member Avatar for xyte

Dear All,
I have a [project] table which includes Project name,Startdate,EndDate,Location.and [SubContractor] table which includes id,subContractor_name. each project has one SubContractor at time and one SubContractor may have many projects at time.

There are cases in which the first SubContractor leaves the project for some reasons and the company give the project to a second SubContractor.

My question is what would be the best way to Normalize or Design these tables. shall I have a middle table(Many-to-Many) or shall include these two fields in [Project] Table called SubCont1_ID,SubCont2_ID(both of them linked to the [SubContractor]table)
Please help me in this issue.

thank you in advance
the above question was answered by Robert but Now:

New Requirements has been added please clear me

The project is going to be issued to a Contractor and that Contractor is going to give the project to another Sub-Contractors there is going to be many subcontractors for one project and one subcontractor may have many projects.

Now how would i maintain the realtionship between Projects,Contractors and SubContractor, Is it going to be Projects-To-Contractor(Many-to-Many),and Projects-to-SubContractor(Many-to-many), Contractor-to-SubContrator(one-to-many) or would be different?

Thank you.

In your [[B]Project[/B]] table, you should include ProjectID field so that you can monitor and assign a Project using ProjectID field. With regards to the [[B]SubContractor[/B]] table, you need contact information for the SubContractor.

To make it normal, you need another table with the following information [ProjectID, SubContractorID, AssignedDate], making a many-to-many relationship. The AssignedDate inside this table will help you determine in the future when the project was assigned to a SubContractor.

Maybe you can also add two more tables so that you can keep track of all the tasks or jobs done on a certain project.

The first table wiill have the following information [TaskID, TaskDescription, DateTaskStarted, DateTaskEnded].

The other table will have the following information [ProjectID, TaskID, Status] --> Status would be [In-Progress, Completed, Not-Completed].

Question: Is your SubContractor a company or just a single individual? If it is a company, then surely you assign a person to a certain task.

I hope this one helps?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.