I'm trying to devise ordering logic for a service similar to Netflix. There are two methods to create an order; there are two major factors that determine if an order should be placed. It's impossible to write this question without considerable explanation, so bare with me.
The first way in which an order can execute is when someone returns an item. Once the order is closed, a new one is created immediately through a series of trigger actions.
The second way is through polling. Once a day the application will scan for any members who qualify to make an order, and create one automatically. This is necessary because, unlike existing members, new members need a mechanism to start and sustain the cycle of closing/creating orders (since new members do not have orders to close).
A simplified version of the schema is as follows:
members(id, name, plan_id) - A members table
games(id, title) - A products table
plans(id, plan, games_at_home, games_per_month) - A plans table
controls(id, member_id, games_at_home, games_this_month) This keeps track of a member's monthly activity to determine ordering eligibility
wishlist(id, member_id, game_id) - This stores a lists of games members store in their queues, which eventually become orders
The factors to determine if an order will take place are the following:
- controls.games_at_home has to be less than plans.games_at_home
- controls.games_this_month has to be less than plans.games_this_month
The polling system is what's giving me trouble. My idea was to scan the database for all users who satisfy the above criteria. Once found, loop through every record and determine how many orders a member qualifies for (deduct controls.games_at_home from plan.games_at_home). Use the result to loop through their games wish list and create the number of orders they qualify for. For example, if the member has a plan where he keeps two games at home, and his controls.games_at_home is 0, then (2 - 0 = 2) he gets two orders, etc...
Then I discovered the logic was flawed if the following scenario happened:
plan.games_at_home = 2, controls.games_at_home = 0
plan.games_per_month = 4, controls.games_this_month = 3
Since the customer has no games at home and his plan allows two, he gets to make two orders, but he's already ordered three times this month, so in reality he/she should only get one order...
If I used plans.games_per_month minus controls.games_this_month the problem reverses itself. What if:
plan.games_per_month = 2, controls.games_this_month = 0
plan.games_at_home = 1, controls.games_at_home = 0
Then two orders are created but the plan only allows for one order.
Is there an easier way to create such logic? Is the controls table a flawed idea? If not, how do I solve this problem?
Is it possible to query the database a single time instead of first create a list of members eligible to order, then loop through their lists?