Conceptually - how would you do this with datetime values in 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Duke Carey - 23 Jul 2008 21:31 GMT

As a learning exercise I decided to try to model out a system that would
accept golf tee time reservations from a website. Didn't take long to
realize that this was a more involved undertaking than I was up to. However,
the preliminary questions wrote down are intriguing, at least to me, and if
anybody else finds them intriguing, perhaps they'd offer their perspective.

Again, this is simply for self-education, not for a real project. People
here devote lots of time and thought to responses really trying to help
people in a bind, and I don't want anyone to that here without knowledge of
my disclosure

So..the logical (i.e. non-SQL) concept I started with is that there'd a
GolfDates table with 4 columns:
GolfDate (specific date on which to accept tee times)
FirstTime (i.e., 7:30 AM)
LastTime (say 4:00 PM)
Interval (# minutes between times)

And a TeeTimes table with 2 columns (oversimplified of course):
TeeTime (smalldatetime)
PlayerID (from a Members table, perhaps)

Well, since SQL has no separate DATE types and TIME types, translating the
first table concept to SQL is a bit troublesome, but I think you really want
to separate the date component from the time component. You might have
different starting tee times for weekdays vs weekends, or for different times
of the year, or if the course was closing early or opening late for
maintenance.

Then, when presenting a grid of times to choose from for a specific date,
you'd want to show all possible times, based on the parameters in the
GolfDates table, calculated on the fly and joined to the TeeTimes table to
show the times that were already booked. And, how do you constrain the
actual teetime in the TeeTimes table to be consistent with the parameters
established in the first table.

Then the next roadblock I hit was, how do you limit a tee time to 4 golfers
only.

So..is anybody else intrigued by this?

Any and all comments are welcome.

Reply to this Message

sloan - 23 Jul 2008 21:44 GMT

I did this one time for a class exercise ....
Mine was doctor appts.

I ended up making a TimeSlot table.
and a TimeSlotGroup table.

I threw in 15 minutes increments into the TimeSlot table.
If every day had the same timeslots, I wouldn't have needed the
TimeSlotGroup table, but I did that so M-F could have certain TimeSlots, and
Sat could have a different TimeSlot(group).

Anyway.

I had a 1 doctor to 1 time slot relationship. So I used a unique
constraint.
I guess you'll have to make a business logic UDF or trigger perhaps to check
for 4 players per TimeSlot.

If you have known timeslots, then I think this is ok.

If you have a true "calendar" program, where the appt can be any time of day
(for any length), I wouldn't go with this approach.

> As a learning exercise I decided to try to model out a system that would
> accept golf tee time reservations from a website. Didn't take long to
[quoted text clipped - 45 lines]
>
> Any and all comments are welcome.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 23 Jul 2008 21:53 GMT

> GolfDate (specific date on which to accept tee times)
> FirstTime (i.e., 7:30 AM)
> LastTime (say 4:00 PM)
> Interval (# minutes between times)

Wouldn't the interval be something that is not really date-dependent? E.g.
You always need to leave 5 or 10 or some n minutes between tee times, and I
don't see why you would *need* it to vary by day. On less busy days you may
have longer intervals naturally, but you couldn't go less than the minimum
of 5 or 10 or some n minutes.

I would use a calendar table for the dates, which could be extended to
identify the type of day for each day, which will tell your application
which time ranges are eligible on that day. (By this I mean that you could
make the ranges more generic based on the type of date (weekdays, weekends,
holidays) instead of storing the ranges redundantly for each date.) You
could also add seasonal types and maybe add exceptions for closing early
cases (which I assume are not all that common).

> And a TeeTimes table with 2 columns (oversimplified of course):
> TeeTime (smalldatetime)
> PlayerID (from a Members table, perhaps)

> Well, since SQL has no separate DATE types and TIME types,

Ah but SQL Server 2008 does! :-)

> translating the
> first table concept to SQL is a bit troublesome, but I think you really want
> to separate the date component from the time component.

I've always hated the kludge of "stripping" the date but storing 1900-01-01
instead. You don't really gain anything by removing this information... you
do not save space, and you make it harder to recognize the data by looking
at that column alone. If you want you can create a view that splits the
information up and makes it easier to display and also join against the
calendar table.

> Then, when presenting a grid of times to choose from for a specific date,
> you'd want to show all possible times, based on the parameters in the
> GolfDates table, calculated on the fly and joined to the TeeTimes table to
> show the times that were already booked. And, how do you constrain the
> actual teetime in the TeeTimes table to be consistent with the parameters
> established in the first table.

This does not sound that complex. I would have to actually code it up to
see what kind of problems you might come across here, but I can't think of
anything right now.

> Then the next roadblock I hit was, how do you limit a tee time to 4 golfers
> only.

Using an after trigger or an instead of trigger you could prevent this (or
if you are starting from scratch, restrict all access to stored procedures,
and put the logic right there... Though having the trigger is still a good
idea just in case). All you really need to see is if the current activity
is trying to make the following query yield results:

SELECT TeeDateTime
FROM TeeTimes
GROUP BY TeeDateTime
HAVING COUNT(*) > 4;

A

Reply to this Message

Duke Carey - 24 Jul 2008 01:28 GMT

Aaron -

Thanks for your thoughts.

I was vaguely aware that 2008 will offer separate date and time types. May
experiment with 2008 Express when available.

The interval may not need to be date dependent, but I was trying to plan
something generic enough to allow a club to push say, 8 minutes between tee
times on busy days, and 10 minutes on slow days. 'Busy' and 'slow' being the
club's forecast for how busy a certain day will be.

It seems to me that the means for *defining* and then spinning out a list of
correct time slots for a given date has to be stored in the system somewhere,
and perhaps that's the most basic of questions here. If those parameters are
stored in the system, and a function or sproc uses those parameters to
control what times get PRESENTED to a user, and another sproc absolutely
controls what data gets INSERTed, then there is no need for a constraint on
the TeeTime column itself.

I'll fiddle with it and see if I can conjure up something that works, then
present it here as a straw man. See what people think. It should be
educational to at least try.

Again, thanks for your time & thoughts.

> > GolfDate (specific date on which to accept tee times)
> > FirstTime (i.e., 7:30 AM)
[quoted text clipped - 60 lines]
>
> A

Reply to this Message

Duke Carey - 24 Jul 2008 01:28 GMT

> > GolfDate (specific date on which to accept tee times)
> > FirstTime (i.e., 7:30 AM)
[quoted text clipped - 60 lines]
>
> A

Reply to this Message

Chris Hohmann - 25 Jul 2008 01:30 GMT

Color me intrigued. Here are some thoughts I had.

1. You could rethink the model for business hours. Instead of using
date/time ranges, capture the datetime instances when the state of the
business operations changes. So instead of GolfDates(GolfDate, FirstTime,
LastTime, Interval), you could have Schedule(start_dt, status, interval).
You can then create a materialized view to calculate the end_dt associated
with each start_dt, which is simply the next date is the Schedule table. The
start_dt column would be the primary key. The benefit here is that you can
eliminate gaps and overlaps without using constraints. It also means you can
adjust an opening or closing time with having to worry about reconciling the
change against the next/previous time range. I like to visualize it as a
bunch of sliders on a timeline. This model also allows for multiple periods
of operation in a given day, ie. open in the morning, closed for lunch, open
again in the afternoon. It also allows for hours of operation that span
multiple days, say a weekend 48 hour golf marathon.

2. Instead of assigning tee times to an individual, you could assign them to
parties. And a party can have 1-4 members. That way you don't have some
whacko inviting themselves into your 3 person group.

3. You'll still want to use a constraint to enforce the minimal interval
rule. But that won't be enough, since it's conceivable that your hours of
operation may change after a tee time has been booked. Blizzard, earthquake,
etc... So you want a conflicts query for tee times booked during
non-operating hours, and some mechanism to cancel/reschedule the booking.

Reply to this Message