Many to Many Relationships; Handling input from "One" Sides
Many to Many Relationships; Handling input from "One" Sides
(OP)
I'll begin by describing my situation verbally, in an effort to avoid unnecessary detail if it's not required.
I am creating a system of tables to track construction bids from an owner's perspective. Within a given project, I need to add potential bidders (Construction Firms). Each project can have any number of Project Alternates defined and bid on by each Firm. Additionally each project can have any number of Unit Costs associated and bid by each Firm.
Ideally I would then be able to have a listing of all projects, associated contractors, and applicable Unit Costs and Alternates.
My difficulty comes not in assigning elligble bidders, or even creating records for Project Alternates or Unit Costs. It comes in creating a many to many relationship that represents all of these so that my project managers can add information such as the amount bid on each component.
My current (applicable) table / relationship structure is as follows:
The other option I had been toying with was to combine the Unit_Bid and Alt_Bid Tables into a singluar Bid_Details table:
Here are the pertinent table structures and fields:
Keep in mind the Bid_Details table was intended to replace the Unit_Bid and Project_Alternates Tables.
Right now I have project managers populating eligible bidders by selecting firms and projects to create the Eligible Bidders table. They also create a project's Unit Costs and Alternates in their respective tables. I need to understand the best way to link these records so that they can begin entering Amounts from the bids into some form of record.
Any help would be greatly appreciated, and I apologize for the length of the request
I am creating a system of tables to track construction bids from an owner's perspective. Within a given project, I need to add potential bidders (Construction Firms). Each project can have any number of Project Alternates defined and bid on by each Firm. Additionally each project can have any number of Unit Costs associated and bid by each Firm.
Ideally I would then be able to have a listing of all projects, associated contractors, and applicable Unit Costs and Alternates.
My difficulty comes not in assigning elligble bidders, or even creating records for Project Alternates or Unit Costs. It comes in creating a many to many relationship that represents all of these so that my project managers can add information such as the amount bid on each component.
My current (applicable) table / relationship structure is as follows:
CODE
Name: ContractorsEligible_Bidders
Table: Contractors
Foreign Table: Eligible_Bidders
PK: Contractor_ID FK:Contractor_ID
Name: ProjectsEligible_Bidders
Table: Projects
Foreign Table: Eligible_Bidders
PK: Project_ID FK:Project_ID
Name: Eligible_BiddersAlt_Bid
Table: Eligible_Bidders
Foreign Table: Alt_Bid
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: Eligible_BiddersUnit_Bid
Table: Eligible_Bidders
Foreign Table: Unit_Bid
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: Project_AlternatesAlt_Bid
Table: Project_Alternates
Foreign Table: Alt_Bid
PK: Proj_Alt_ID FK:Proj_Alt_ID
Name: Unit_PricingUnit_Bid
Table: Unit_Pricing
Foreign Table: Unit_Bid
PK: Unit_Price_ID FK:Unit_Price_ID
Table: Contractors
Foreign Table: Eligible_Bidders
PK: Contractor_ID FK:Contractor_ID
Name: ProjectsEligible_Bidders
Table: Projects
Foreign Table: Eligible_Bidders
PK: Project_ID FK:Project_ID
Name: Eligible_BiddersAlt_Bid
Table: Eligible_Bidders
Foreign Table: Alt_Bid
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: Eligible_BiddersUnit_Bid
Table: Eligible_Bidders
Foreign Table: Unit_Bid
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: Project_AlternatesAlt_Bid
Table: Project_Alternates
Foreign Table: Alt_Bid
PK: Proj_Alt_ID FK:Proj_Alt_ID
Name: Unit_PricingUnit_Bid
Table: Unit_Pricing
Foreign Table: Unit_Bid
PK: Unit_Price_ID FK:Unit_Price_ID
The other option I had been toying with was to combine the Unit_Bid and Alt_Bid Tables into a singluar Bid_Details table:
CODE
Name: Bid_DetailsProject_Alternates
Table: Bid_Details
Foreign Table: Project_Alternates
PK: Bid_Detail_ID FK:Bid_Detail_ID
Name: Bid_DetailsUnit_Pricing
Table: Bid_Details
Foreign Table: Unit_Pricing
PK: Bid_Detail_ID FK:Bid_Detail_ID
Name: ContractorsEligible_Bidders
Table: Contractors
Foreign Table: Eligible_Bidders
PK: Contractor_ID FK:Contractor_ID
Name: Eligible_BiddersBid_Details
Table: Eligible_Bidders
Foreign Table: Bid_Details
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: ProjectsEligible_Bidders
Table: Projects
Foreign Table: Eligible_Bidders
PK: Project_ID FK:Project_ID
Table: Bid_Details
Foreign Table: Project_Alternates
PK: Bid_Detail_ID FK:Bid_Detail_ID
Name: Bid_DetailsUnit_Pricing
Table: Bid_Details
Foreign Table: Unit_Pricing
PK: Bid_Detail_ID FK:Bid_Detail_ID
Name: ContractorsEligible_Bidders
Table: Contractors
Foreign Table: Eligible_Bidders
PK: Contractor_ID FK:Contractor_ID
Name: Eligible_BiddersBid_Details
Table: Eligible_Bidders
Foreign Table: Bid_Details
PK: Bid_Tab_ID FK:Bid_Tab_ID
Name: ProjectsEligible_Bidders
Table: Projects
Foreign Table: Eligible_Bidders
PK: Project_ID FK:Project_ID
Here are the pertinent table structures and fields:
CODE
Alt_Bid
Alt_Tab_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong Indexed
Proj_Alt_ID dbLong Indexed
Proj_Alt_Amount dbCurrency
Bid_Detail_Type
Bid_Detail_Type dbLong PrimaryKey Indexed
Bid_Detail_Desc dbText
Bid_Details
Bid_Detail_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong ForiegnKey Indexed
Bid_Detail_Type dbLong
Bid_Detail_Amount dbCurrency
Contractors
Contractor_ID dbLong PrimaryKey Indexed
Contractor_Firm dbText
Eligible_Bidders
Bid_Tab_ID dbLong PrimaryKey Indexed
Project_ID dbLong ForiegnKey Indexed
Contractor_ID dbLong ForiegnKey Indexed
Bid_Amount dbCurrency
Project_Alternates
Proj_Alt_ID dbLong PrimaryKey Indexed
Bid_Detail_ID dbLong ForiegnKey Indexed
Project_ID dbLong Indexed
Alt_Number dbLong
Alt_Desc dbText
Projects
Project_ID dbLong PrimaryKey Indexed
Building_ID dbLong Indexed
Project_Name dbText
Project_Number dbText
Unit_Bid
Unit_Tab_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong Indexed
Unit_Price_ID dbLong Indexed
Unit_Amount dbCurrency
Unit_Pricing
Unit_Price_ID dbLong PrimaryKey Indexed
Bid_Detail_ID dbLong ForiegnKey Indexed
Project_ID dbLong Indexed
Unit_Price_Number dbLong
Unit_Price_Desc dbText
Alt_Tab_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong Indexed
Proj_Alt_ID dbLong Indexed
Proj_Alt_Amount dbCurrency
Bid_Detail_Type
Bid_Detail_Type dbLong PrimaryKey Indexed
Bid_Detail_Desc dbText
Bid_Details
Bid_Detail_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong ForiegnKey Indexed
Bid_Detail_Type dbLong
Bid_Detail_Amount dbCurrency
Contractors
Contractor_ID dbLong PrimaryKey Indexed
Contractor_Firm dbText
Eligible_Bidders
Bid_Tab_ID dbLong PrimaryKey Indexed
Project_ID dbLong ForiegnKey Indexed
Contractor_ID dbLong ForiegnKey Indexed
Bid_Amount dbCurrency
Project_Alternates
Proj_Alt_ID dbLong PrimaryKey Indexed
Bid_Detail_ID dbLong ForiegnKey Indexed
Project_ID dbLong Indexed
Alt_Number dbLong
Alt_Desc dbText
Projects
Project_ID dbLong PrimaryKey Indexed
Building_ID dbLong Indexed
Project_Name dbText
Project_Number dbText
Unit_Bid
Unit_Tab_ID dbLong PrimaryKey Indexed
Bid_Tab_ID dbLong Indexed
Unit_Price_ID dbLong Indexed
Unit_Amount dbCurrency
Unit_Pricing
Unit_Price_ID dbLong PrimaryKey Indexed
Bid_Detail_ID dbLong ForiegnKey Indexed
Project_ID dbLong Indexed
Unit_Price_Number dbLong
Unit_Price_Desc dbText
Keep in mind the Bid_Details table was intended to replace the Unit_Bid and Project_Alternates Tables.
Right now I have project managers populating eligible bidders by selecting firms and projects to create the Eligible Bidders table. They also create a project's Unit Costs and Alternates in their respective tables. I need to understand the best way to link these records so that they can begin entering Amounts from the bids into some form of record.
Any help would be greatly appreciated, and I apologize for the length of the request
Talk To Other Members
RE: Many to Many Relationships; Handling input from "One" Sides
Combining Unit_Bid and Alt_Bid into a single table seems sensible from a database and development standpoint. Sounds like you have a bid that can alternate status - it can be either Unit or Alt, but not both. If so, I suggest adding a status field to the Bid_Details table and then allow designation of whether the bid is Unit or Alt.
Cheers,
formertexan