How should I implement this credit system?

I am creating a website where members of the family can book a vacation in the family's sea cottage. Each member is assigned 40 credits each year. A member must have enough credit points to book a vacation. (this credit system makes sure every member has equal opportunity to stay at the cottage) However, not all weeks require the same amount of credit points, and last minute bookings require no credit points.

My question is: how should I store the credit points in the database? What is most efficient? Per day/per couple of days/per week? And how do I efficiently check how much credit points are needed for a given date range using mysql?

BTW: I am using the Zend Framework and I am familiar with the code etc., I just need some开发者_StackOverflow中文版 conceptual advice ;)

I don't know if I understand right the problematic here but :

• You need to store in a "users" table the amount of credit remaining for each user.
• You need to have another table with fields like "start period", "end period", "duration", "amount of points needed"
• In the duration field, it could be an enum with values like "day", "week", "last_minute".

Then the logic will be in your PHP code, but with that, you'll be able to have the right number of credits needed whenever the user wants to use the cottage.

If I understand your case correctly, I think I would store something like:

• number of remaining credits per user
• a default number of credits per day
• number of days before that counts as "last minute"
• periods of deviating credit cost, with (start date, end date, cost per day)

Then, I would calculate the cost to book dynamically from this information whenever the user needs to make a booking. The last minute booking rule can be seen as a special deviating period where start date is now, end date is now+"number of days" and cost is zero.

I assume that no-one will book the cottage for a shorter duration than one day. If they will, I would change the use of "day" above to whatever unit of time would be sensible.

Just my thoughts. :-)