Page 1 of 1

best way to store price structure

Posted: Mon Jan 15, 2007 9:16 am
by speedy33417
I'd like your input on how it would be the easiest to store price structure in a db.

Unfortunately, it's a quite complicated because there's several set up. It's for a service company.

1) There's the easy one. Hourly rate.
2) Flat rate for the first 3 hours, then hourly rate
3) Monthly billing. Say twice a week service, but because of the different months it could be 7-9 billable jobs for the same monthly price.

I'm in the process of building the database for them. They'd like to have a log in system for their clients where they can access previous and upcoming appointments, balance due on their account, charges and payments, and invoice view, etc.
95% of their clients are charged hourly (option 1) and it would make my life a lot easier if it was just that. However the rest of the 5% will have to be worked in somehow as well.

What is the best way to store this in a database?

Right now I'm thinking of doing job_price VARCHAR(10) and process the value based on the first character:

H-40 --> Hourly customer. $40/hour
F-150/40 -->Split price set up. $150 for the 1st 3 hours, then $40/hour
M-525.00 -->Monthly rate of $525.00

Any thoughts?

Posted: Mon Jan 15, 2007 9:17 am
by Kieran Huggins
Maybe you could store it as a serialized javascript or php function?

Posted: Mon Jan 15, 2007 9:23 am
by speedy33417
If I do it the way I described I'll definately write a function to process it.

I was just wondering if I overcomplicate it and there's an easier way to do this.

Posted: Mon Jan 15, 2007 9:37 am
by feyd
It would appear you could have "type," "price," "units of measure," and "parent" fields. Type would be what type of information is in the record; units of measure could be hourly or 5 minutes; parent would refer to the main record (for split style)