Database Design for Fee Schedule

[insight974]

Hello, I'm building a structure for a fee schedule for a construction business. let's say that the client is paying for roof. THen, the way they charge is: they charge $7.00 for the first 1000 feet and 5.00 for the rest. With this in mind, I created 4 columns: Base Fee, Base Units, Variable Fee and Variable Unit and I entered the values: 7.00 for base fee, 1000 for base amount, 5.00 for variable fee and 1 for variabe units (you know, the normal financial formula for this). However, now the user came with some moe clarification and it's more complex. For example, it could be 7.00 for the first 1000 feet, $5.00 for 1001-999 feet and $3.00 for the rest. Now I'm frozen and don't have a clue on what to do. Please help!!!
Thanks everyone

[timothybard]

Are the fees per Square Foot? Here is the structure I would recommend:

Fee Table:
ID (PK)
Item (PK pointing to the work that is done (roof, floor, etc)
Fee (currency)
Lower Bound (int, the minimum area this charge applies to)
Upper Bound (int, the maximum area this charge applies to)
Running Total of Lower rates (the amount due for the work done at lower rates).

Using your example, here are the records needed:

ID, 1 (Roof), $7.00, 0, 1000, 0
ID, 1 (Roof), $5.00, 1001, 1999, $7,000
ID, 1 (Roof), $3.00, 10000, NULL, $11,990

[timothybard]

I actually wouldn't use the Running Total of Lower rates I mentioned in my previous post, but it does make things easier when pulling queries together.

[insight974]

Great!! THat's exactly the idea I needed. I really appreciate it