best way to store price structure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

best way to store price structure

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Maybe you could store it as a serialized javascript or php function?
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
Post Reply