How can I store resource available by date (year/month/day/our) in database? -

I have a program that manages resources (agent). There is a facility to select the date of the calendar in the program (Example: 08/11/2013 5) and get the number of resources available at the selected time. How can I handle the status of resources for every hour of the day in a relational database effectively?

Resource status is just a string: "accessible" or "busy"

Do you have any ideas or strategies for this?

Thanks for the help!

The best way to store the boundaries of the effective date (/ time) is by date (/ time) In other words, in the other words, store the records with an effective date / time and an expiration date.

Do not attempt to break time in sections with records for each. It is inflexible and heavily loads your data maintenance badly.

So for your case, where you want to know when an agent is available, then there is a record structure like this:

• Agent_ID < Li> Expiry_Datetime

  Note that you do not need the status ("Accessible" vs. "Busy") since the agents should be accessible in the table. It is assumed that if any Agents are not effective at any particular point in time, they should not be accessible.

  If you want to know which agent is available on 08/11/2013 05: 00 then just query with a WHERE clause which includes: WHERE Effective_Datetime & lt; = '2013-08-11 05:00' and Expiry_Datetime & gt; = '2013-08-11 05:00'