mac | pc Assigning then duplicating records based on checkbox selections

[weststreet]

Hi. I have a table called PRICING that contains a field PRICE. I have a list of vendors in a table called VENDORS. Depending on the project, I may want 5 or so vendors (selected with checkbox field) to enter their price in the field PRICE. So its the same record, but the field PRICE would be separate for each vendor. Do I determine which vendor is checked, and then duplicate each record with PRICE, and add the specific vendors foreign key to it, thus creating a duplicate for each vendor? Thanks.

[doughemi]

You need a table for Projects, one for Vendors, and a join table for Project_Vendor.

 

Each Project_Vendor record will contain one quote/purchase from one vendor. This table will need, at a minimum, a ProjectID foreign key, a VendorID foreign key, a quote/order selector radio button field, a date field, a price field, and a Purchased checkbox.

 

This is the standard construction for any solution where many of one thing must be related to many of another (in this case, many Projects related to many Vendors).

 

With this schema, it will be easy to create layouts to show all vendors on a project, or vendors submitting a price within a certain time period, or all projects bid on by a vendor, or many other reports.