# logic question -using two tables to display bookings

## Recommended Posts

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.

##### Share on other sites

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.

##### Share on other sites

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|         |
|             +-----------+         |
|                                   |
|                                   |
```

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×

• #### Activity

• Chat
×
• Create New...