logic question -using two tables to display bookings

[peasepud]

OK so I may not explain this well, apologies in advance.

 

The query however is not a coding one, Im happy to sort that out but more a pick of your brains on how you would go about doing this logically.

 

Im working on a booking system for a group of photographers, each photographer works varying shifts (each day can be different) so I have a table of "availability" which contains the following:

 

Photographer Id

Date

Start Time

End Time

 

straight forward, so Mary may have the following entry

56, 2015-10-28, 09:00, 16:00

showing shes working from 9 til 4 on the 28th

 

Im outputting these on a bar style page for each day with the 24 hours at the top and I create a div from midnight until the start time (each hour is 45pixels wide + 1 pixel border) so it calculates the width as 9 x 46 and creates a simple grey div to denote time before work.

 

Then theres a 2nd table of bookings which shows the jobs the clients are booked on, this contains

Photographer_id

Date

Start Time

Duration

 

So after creating the 1st div, I pull all the bookings for that client in the day and create divs to show they're busy. Same logic, calculate the width of the div based on the length of the job (1 hour = 45pixels), calculate the time between the last job (or them starting work) and set the left margin to that amount.

 

eg Mary has a 1 hour job at 10am would create a div of 46 px wide and set its left margin to 46px (10am - 9am = 1hour).

I do this for all the jobs and then finish off with another grey div to show theyre not working using the same logic (24:00 - 16:00 = 8hours so set the width to be 8x46).

 

So my basic logic is:

For each record in availability table for requested date

        Create Pre start div

             Cycle through any bookings creating booking divs

        Create Post end div

Loop

 

That all works great however......................

 

they have a new contract that means they need to provide cover 24 hours a day so now a person could be working say 8pm until 4am and their availability table would show:

56, 2015-10-28, 20:00, 04:00

which wouldnt make sense so instead Im creating two records.

56, 2015-10-28, 20:00, 23:59

56, 2015-10-29, 00:00. 04:00

 

this however means that Mary can now have two records for the 29th, the one above and then the next shift:

56, 2015-10-29, 20:00, 23:59

 

Now no matter how many times Ive ran this through in my head I cant come up with how best to change the display page to incorporate this logic.

 

The only thing I can see to do would be to remove the availability table all together and create records showing times they arent available ie for Marys 9 to 4 shift, I would add two records to the bookings table (with a new indicator to denote OFF) of

56, 2015-10-28, 00:00, 08:59

56, 2015-10-28, 16:00, 23:59

 

 

Is this the best way forward or is there some easier way you can see to incorporate multiple shifts in a day?

 

Any help would be appreciated.

 

 

[0x00]

I generally use unix time, so its seconds since something like new years 1971.

 

Calculate your start and end times for the day(?).

 

Then use those times to check against the start and end times of booked blocks.

 

If a retrieved block extends before or after given period you can easily indicate / link such fact.

 

 

 

So... no I personally wouldn't add two entries in this type of situation. If it was based on working days without rollover then I would.

[Barand]

Moving to Application Design forum.

 

I would use a 36 hour day instead of a 24 hr day.

 

If the end time < start time, add 24hr to end time (so 20:00 - 04:00 becomes 20:00 - 28:00)

 

Now you can have a shift/booking as a single div as before

|                                   |  
00    04    08    12    16    20    00    04    08    12
|                                   |                  
|                                   |
|                              +-----------+
|                              |20:00-28:00|
|                              +-----------+
|                                   |
|             +-----------+         |
|             |09:00-16:00|         |
|             +-----------+         |
|                                   |
|                                   |