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.