943,923 Members | Top Members by Rank

Web Development > Databases > Database Design > Database Design and normalization new requirements?
Ad:
Permalink
Aug 1st, 2007
0

Database Design and normalization new requirements?

Expand Post »
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.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
atal is offline Offline
24 posts
since Jul 2007
Permalink
Sep 13th, 2007
0

Re: Database Design and normalization new requirements?

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.
Featured Poster
Reputation Points: 480
Solved Threads: 276
Postaholic
Ramy Mahrous is offline Offline
2,189 posts
since Aug 2006
Permalink
Sep 13th, 2007
0

Re: Database Design and normalization new requirements?

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.
Reputation Points: 20
Solved Threads: 6
Junior Poster in Training
Temmu is offline Offline
84 posts
since Sep 2007
Permalink
Sep 20th, 2007
0

Re: Database Design and normalization new requirements?

Click to Expand / Collapse  Quote originally posted by 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.
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?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
xyte is offline Offline
15 posts
since Sep 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Database Design Forum Timeline: Inventory Management DAtabse
Next Thread in Database Design Forum Timeline: IBM reveals a medical Google Earth





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC