I’m helping someone put together a website that helps schedule fitness instructors, and I’m trying to figure out how to store hours of availability in the database. Right now we have a table that holds the instructors, but I’m not really sure how to store their availability in a way that will be easily searchable using a web form.
Would I show instructor ID, date, start time, end time? Or is there a better way?
well, if you store date, you’ll have to ahve a row for every day you expect each instructor to be available
use a SMALLINT day column instead… 1=sun - 7=sat
presumably you will not have to worry about a shift that crosses midnight, right?
Most of the shifts will be during normal business hours. Eventually we might have to accommodate timezones, but just in North America. If we do weekdays (Mon, Tues, Wed) then I’m wondering how to handle booking time weeks in advance. Maybe have a week number (1-52)?
[quote=“cydewaze, post:3, topic:107201, full:true”]…then I’m wondering how to handle booking time weeks in advance. Maybe have a week number (1-52)?
[/quote]
that’s a different question, you asked about availability schedules, not bookings 
Sorry, I misunderstood the purpose of what they wanted (I over-thought it as I often do). This table is indeed to show general hours of availability (ex 9-5 on Monday, 10-5 on Tuesday, etc)
They already have software to do the bookings.
I wonder if this would be a good use of EAV.
[instructors]
ID (int)
firstname (varchar)
lastname (varchar)
[av_vars]
ID
day (int)
starttime
endtime
[av_values]
instrID (int)
varID (int)
value
I’m trying to think of a way that makes them searchable, as opposed to a text value like “9-5” which would only be good for display.
that’s not EAV
i would do it without the av_values table… just move instrID into av_vars and rename it avs… drop avs ID column, and make instrID + day as the PK
[quote=“cydewaze, post:6, topic:107201, full:true”]I’m trying to think of a way that makes them searchable, as opposed to a text value like “9-5” which would only be good for display.
[/quote]
this design ~is~ searchable…
WHERE starttime BETWEEN '11:00' AND '13:30'
AND endtime >= '16:00'
Oh hmmmm, that’s very interesting. I was trying to figure out how to get the day in there.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.