I'm attempting to implement a system that limits the amount of total orders the system can accept for for a given timeframe.
Scenario: a business can only fulfill 5 orders per hours. Once that timeframe is filled, no more orders can be accepted. This feature would reset daily.
High Level Design:
Business Table – holds business information, etc. (structure: id(INT), name(VARCHAR), address(VARCHAR))
Timeframe Table – holds available timeframes for the day. (structure: id(INT), start_time(TIME), end_time(TIME))
Business Timeframes Table – reference business with available timeframes. (structure: id(INT), business_id(INT), timeframe_id(INT)).
Having set this up, I can assign a business with available timeframes. To create a counter, I'm thinking that I should create another table, which references a business_id, timeframe_id, available, filled, and possibly datetime/timestamp.
Since the counter would reset daily, and there is a possibility of implementing future orders (deliver for today vs tomorrow vs day after), what would be the best way to go about this design?
Best Answer
First, you have to figure out how you want to define the system: is it the order date that is constrained by your timeframes, or is it the delivery date?
The design seems overly complicated to me. You could just store the relevant timestamp with the orders and error out if there are more than five in one hour.