syscomments

RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

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. 

Print | posted on Thursday, February 22, 2007 12:04 PM |

Feedback

Gravatar

# 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'
2/22/2007 2:20 PM | Jeff
Gravatar

# 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.
2/22/2007 3:08 PM | Jeff
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET