Table structure for a database holding tendering data for 2 suppliers

[handlebars]

I am a junior database developer and have managed to get myself totally stumpted with a database system for a council department.

The problem i am having is structuring the tables for a tendering process, where a list of items is selected for a job and using current prices for each supplier - the users need to be able to create two estimates based on these.

Although the items and quantities are the same for both suppliers, their are different discounts that can be applied on the whole order for each supplier.

I understand that I have to have a many to many relationship between estimates and suppliers (as one estimate always has many suppliers; each supplier has many estimates).

The other issue is that prices are changed every few months, but price history must be retained.

I know this is a simple overview of the system, that may have lots of other intricacies, but in essence i need to know if their is an standard way of modelling this without having to use lots of messy append queries (which is the only way I can acheive this at present!)

What I am hoping the best structure will allow is for users to create the tender list and add the relevent discounts for the two suppliers and the query could lookup the current item prices for the two different suppliers and return a total estimate cost.

I accept that what i am looking for may be impossible, but it would be very nice to get some feedback from anyone that has dealt with this type of problem before.

Thanks in advance

Andrew

 

[willir]

handlebars

Hmmm, tough one.

Here is what I have for a basic design. (Hopefully, it is close to your design)

Basics...

SupplierTbl
SupCode - pk, text
SupName
+ other info for supplier

ItemTbl
ItemCode - pk, text
ItemDesc
+ other info for item

JobTbl
JobId - pk, numeric
JobDesc
JobDate
+ other info for Job

Okay, now for the relationships.

JobDetailTbl or Job Item
JobID - fk to Job
ItemCode - fk to Item
ItemQty

primary key - JobID + ItemCode

EstimateTbl
EstimateID - pk
SupCode - fk to Supplier
JobId - fk to Job
EstDiscount - discount for entire order

SupCode + JobID - unique index to prevent multiple esitamted for the same bid. I am using EstimateID as the primary key because it make linking to the EstDetailTbl. But you can also use SupCode + JobID as the primary key and link on the two fields for the detail table.

Note: If a supplier can submit more than one bid, then you may consider adding a date or date/time field and add it to the Unique index for SupCode + JobID

EstDetailTbl
EstimateID - fk to Estimate
SupCode - fk to Supplier
ItemCode - fk to Item
ItemPrice

primary key - EstimateID + SupCode + ItemCode

Then, your supplier pricing table - used for getting the current price for an item.

SupItemPrice
SupCode - fk to Supplier
ItemCode - fk to Item
PriceDate - date or date/time
Price

primary key - SupCode + ItemCode + PriceDate

The way it works...

1) A Job profile is prepared (Job + Job Detail)
2) Supplier updates pricing and includes estimate discount to start the estimate process (SupItem, Estimate)
3) The system, using the JobDetail and the Estimate table to generate estimate detail table (EstDetail)

I am not sure of the dynamics for your needs - your "date" fields might need to include date + time to allow a rebid on the same day.

I am not certain which is the best way to define the primary key for the estimate table - unique numeric, or Supplier Code + JobID. I opted for a simple link between the Estimate and Estimate detail table, although the other approach would work. I would have to work at the problem at the form and report levels to see which is best.

You have probably stewed on this one for a bit. So you may have a better design, but perhaps I have given you some ideas.

Richard

 

[handlebars]

Richard

Many thanks for the detailed reply, I will have a bash over the next day and see how well it works, wlth this structure will it be possible for users to only enter in items and quantities once for each job?

Regards

Andrew

 

[willir]

It should be able to.

When you setup a job - a template so to speak, it will be possible to generate the prices for each vender - but you have to write the code to achieve this.