Many to Many Relationships; Handling input from "One" Sides

[kjschmitz]

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:

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

The other option I had been toying with was to combine the Unit_Bid and Alt_Bid Tables into a singluar Bid_Details table:

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

Here are the pertinent table structures and fields:

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

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

 

[formerTexan]

Hello kjscmitz,

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