Solved

# Storing Cost Estimate Details

Posted on 2013-09-30
Medium Priority
256 Views
Hello,

I am creating a data structure to capture cost estimate data from an existing spreadsheet/template whose functionality we're going to replace via a web form.  I have created a table structure and it will work, but I'm not happy with it.  I'm sure there has to be a better way.  Perhaps my coffee hasn't kicked in yet.

Most of the records are simply a cost code and a total:
100        \$5,000
200        \$2,500
300        \$150

However, some of the records are multiple fields that calculate a total for the user, and we'll need to store each of those individual data points rather than the total.
400       \$300/day * 4 days
500       \$150/day * 12 days + \$500
600       450 gallons * \$4/gallon

Here's what I came up with.  Again, it is functional, but i'm not happy with it:

tblUnitOfMeasure
UOM_ID  INT IDENTITY PK
UOM_Name

tblCostEstimate
Cost_Estimate_ID INT IDENTITY PK

tblCostCode
Cost_Code_ID INT PK

tblCostEstimateDetail
Cost_Estimate_ID INT PK, FK
Cost_Code_ID INT PK, FK
First_Amount FLOAT
First_UOM INT FK
First_Operator CHAR(1)        (i.e. add, subtract, multiply, divide)
Second_Amount FLOAT
Second_UOM INT FK
Second_Operator CHAR(1)
Third_Amount FLOAT
Third_UOM INT FK

Thanks in advance for any ideas/help.
0
Question by:Torrwin
• 2

LVL 70

Expert Comment

ID: 39534116
Any table with "xxx1"/"xxx2"/ "xxx3" likewise "first"/"second"/"third" is a dead give-away that the table is not normalized.

Each operator and amount need stored in a separate table with a sequence#.  Yes, it's a pain, but experience has proven that anything else is genuinely unworkable over time.

A minor point, the UOM should likely be smallint rather than int.
0

LVL 13

Accepted Solution

Torrwin earned 0 total points
ID: 39574811
I definitely agree it's not normalized.  I ended up creating a formula column and a function that will insert the appropriate values into each formula.
0

LVL 13

Author Closing Comment

ID: 39585815
It's not perfect, but it worked in this specific instance.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.