Database Design and normalization

[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

[CertGuard]

I suppose that all depends on if you want to keep records of the first SubContractor that was associated with the job.

If you don't care about keeping that information, then you won't need a middle table, and all you would need to do is add [SubContractor.ID] (as a lookup) to the [project] table.

If you do want to keep track of the 'transactions' between the projects and SubContractors, then yes, I recommend a middle table with the following fields [ID], [SubContractor.ID], [Project.ID].

[atal]

I suppose that all depends on if you want to keep records of the first SubContractor that was associated with the job.

If you don't care about keeping that information, then you won't need a middle table, and all you would need to do is add [SubContractor.ID] (as a lookup) to the [project] table.

If you do want to keep track of the 'transactions' between the projects and SubContractors, then yes, I recommend a middle table with the following fields [ID], [SubContractor.ID], [Project.ID].

Thank you Robert, Yes I want to keep the record and will create a third table, but How can i display these information in one row, I mean my information should be displayed as "Project ID,Project name, Project Location,SubContractor1,SubContractor2". and how do i know that this was the first subContractor.

Thank you

[CertGuard]

Lets name this third table [Transactions].

[Transactions] is only used as a reference point between [Project] and [SubContractor]. In the [Transactions] table you will only show the reference to [Project.ID] and [SubContractor.ID]. Prefix the records with [Transaction.ID] and you have your reference for the order of SubContractors.

Inorder to link the information, you will need to create Lookups for each of the fields (this is done in design view). These Lookups won't actually give you all of the data you require in the [Transactions] table, but will give you a reference point for the other two tables. And the order in which the data was entered into the [Transactions] table will give some semblance of which SubContractor was #1, #2, etc.

How you will display this information is a matter of how you design your Form to do so. I generally write all my code in ASP, and have rarely (outside of classroom) used the Access Forms.

HTH

[atal]

Ok Thank you very much. I really appreciate your quick response.

once again thank you

[atal]

Sorry for getting late to the thread actually was on leave for the last few days. I have another problem just faced with:

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.

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) or would be different?

Please clear me in this thank you.