Limiting values based on related fields
I have a design issue with my database. What I need is a way to limit the value of a field based on the value in another field, and then I need to have it auto-update when the limiting value increases.
Members in my club earn points based on what they accomplish, called status. This status is spent to give them certain privileges in the club. We need a system where the amount of status spendable is limited by the types of activities the members participate in. For example, when a member earns play status, cast status, and service status, his spendable status goes up (they all feed into the one pool). What we would like to do is automate the limits on spendable status: members can earn as much cast and service status as they want, but can only spend a matched amount based on play status.
Here is an example of what I mean:
Bob has earned 500 play status, 700 cast status, and 200 service status.
His spendable status should be 1200 (500 play, 500 cast, 200 service), with 200 unavailable (left over from cast). Once he spends his cast status, I'd like the database to be able to automatically move the 200 unavailable status to spendable. I guess the trick lies in the fact that the database would have to know to take from service and cast first, then from play.
Does that make sense? If so, is there any way to make a database do this?