INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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:

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

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

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

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


 

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close