i am creating a program that will allow me to work with employee availability and create weekly schedule. I want to use databases to do so. My original idea was to create a database for availability. this would have each employee's ID number and MondayIn, MondayOut, TuesdayIn, TuesdayOut, etc. However the business this program is being written for opens at different times every day and it is very common for an employee availability to simply be Open to 5. or 3 to close. Therefore my timein data could not be stored as "TIME" and would make it difficult to perform searchs based on time data. What would be good way to store the fact that Employee A can work open to close, while Employee B can work 9 to close and Employee C can work 10 AM to close?
what if you assigned an identifier or a bit field , or a column, for each hour or half hour of the working day. for example, 8, 9, 10, ...5 could be mapped to 'bits' in an string expression. 1111111111 to mean all day, or 0000011111 to mean from 1 to 5 pm.
I like the idea however if a person says they can "close"how long should the string be as it is common for a closing shift to go until 2, 3, or even 4 the next morning
This is possibly where you want two extra columns, as flags.
An "open" column and a "close" column. If the flags are set on these then the open_time or close_time TIME fields aren't used. If they aren't set, then the TIME fields are used.