Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Data Structures I'd Like to See

I want to see a structure that I can resolve the following for without using a table scan:

Find me all of the appointments that Amy, Bob, Charlie and Dyan have been in that have ever occurred on Wednesdays in July, August, or September between 8 AM and 3 PM between 1/1/2003 and 12/31/2006.

Find a solution that's a better order than O/2, where O is the table size.

The key to the problem is how we're storing dates, and the fact that without doing something crazy with a star schema kind of model it's going to be very difficult to get this information back in a reasonable time, and using a star schema here would be ill advised since it's an OLTP system, sorta.  The way to handle this in a relational model would be, I guess, to store year, month, day, hour in separate fields and index them, with a clustered index on the person with the appointment. 

Legacy Comments


Jeff
2007-02-22
re: Data Structures I'd Like to See
If months, days of the week, or times are very important to your data and to performance, then you should store them somewhere.

If you create a dates table that covers all dates, all of your date columns are now FK's to this table. In your master dates table, store the day of week, month number, year, etc -- everything you need to quickly look up a date based on that info.

As for time portion of your datetimes, you should now store your times in a separate DateTime column as I demonstrate here:

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

It may help to use the functions shown here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Now, if everything is well indexed, you can quickly query your system like this:

select App.*
from Appointments App
inner join Dates on App.Date= Dates.Date
where
Dates.Month in (7,8,9) and
Dates.DayOfWeek = 3 and
App.Time betwen '8:00 AM' and '3:00 PM' and
Dates.Date between '1/1/2003' and '12/31/2006'

Jeff
2007-02-22
re: Data Structures I'd Like to See
me again:

also, note that the last line in the WHERE could even be replaced with:

Dates.Year between 2003 and 2006

If Year is a column in your Dates table.