Designing a tool for estimating project bids

Question

This tool will be used to estimate project costs for clients. These are not necessarily software projects. Currently there is an Excel spreadsheet with a number of categories with individual tasks for each category. Each of these has some sort of weight or constant involved and is partially responsible in determining the final bid estimate. Each of these tasks has attributes for setup hours, setup cost, operation hours and cost, etc. Currently there is no strictly defined method of coming up with the bids.

For each new bid, a copy of the spreadsheet is made and modified to most accurately portray what we think the costs and hours will be for the project.

Tool requirements:

• Need to be able to add new categories/tasks
• Need to be able to save a bid and recall it when necessary
• Need to be able to modify current weights/constants

At the moment I could easily design a database schema and an ASP.NET page layout that could handle this scenario if it wasn't for the fact that you can change the weights/constant over time. I'm thinking (not a fact) that once you save a bid, all of the constants/weights/costs should be saved along with it somehow. This also poses the problem of displaying the bid, how will I be able to determine how to display it if there are 90 versions of the data running around?

Any ideas are appreciated.

PS: No idea how to tag this.

Answer 1

If you do want to expand it into a full-fledged application I think your proposed solution is good. I agree that you should save the parameters with each bid. This is not really a problem and can be achieved with a single "base" table of default parameter values that could be modified on a per-bid basis.

I would store the bid-specific parameters in another table (i.e. BidSettings or BidParameters.) Refer to the bid-specific parameters in this table by a foreign key to the Bids table and you're done.

Answer 2

To keep track of changing conditions, one approach is to implement a temporal database to store data the complete history of the settings. Wikipedia actually has one of the most succinct explanation I've every read.

To summarize the article, let's say you have these Bid and BitT tables:

TABLE BidT
   uniqueidentifier Id,
   uniqueidentifier BidId,
   int Version,
   int WeightA,
   int WeightB,
   int WeightC

TABLE Bid
   uniqueidentifier Id,
   int BidNumber,
   ...

In this example, the data that does not change or that you don't need to track stays in Bid. Anything that changes which you need to keep track of over time goes into BidT, The T suffix is for Temporal. That way you are only keeping copies of things that change, not duplicating all your data associated with every bid.