Storing Cost Estimate Details

Posted on 2013-09-30
Medium Priority
Last Modified: 2013-10-20

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:


  Cost_Estimate_ID INT IDENTITY PK

  Cost_Code_ID INT PK

  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.
Question by:Torrwin
  • 2
LVL 70

Expert Comment

by:Scott Pletcher
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.
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.
LVL 13

Author Closing Comment

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

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question