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.

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.

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.

In your [Project] table, you should include ProjectID field so that you can monitor and assign a Project using ProjectID field. With regards to the [SubContractor] 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?