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?