mysql - storing a range of values

Question

I have a resource that has a availability field that lists what hours of a day its available for use?

eg. res1 available between 0-8,19-23 hours on a day, the range here can be comma separated values of hour ranges. e.g are 0-23 for 24 hour access, 0-5,19-23 or 0-5,12-15,19-23

What's the best way to store this one? Is char a good option? When the resource is being accessed, my php needs to check the current hour with the hour defined here and then decide whether to allow this access or not. Can I ask mysql to tell me if the current hour is in the range specified here?

Answer 1

I'd store item availability in a separate table, where for each row I'd have (given your example):

id, startHour, endHour, resourceId

And I'd just use integers for the start and end times. You can then do queries against a join to see availability given a certain hour of the day using HOUR(NOW()) or what have you.

(On the other hand, I would've preferred a non-relational database like MongoDb for this kind of data)