Storing Cost Estimate Details


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.
LVL 13
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
TorrwinAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TorrwinAuthor Commented:
It's not perfect, but it worked in this specific instance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.