Is it better to keep Days of month, Months, Year, Day of week and week of year as separate reference tables or in a common Answer table? Goal is allow user content searches and action analytic to be filtered by all the various date-time values (There will be custom reporting for users based on their shared content). I am trying to ensure data accuracy by using IDs, and also report out on numbers of shares, etc by time and date for system reporting by comparing various user groups. If we keep in separate tables, what about time? A table with each hour, minute and second also needed?
A:
Most databases support some sort of TIMESTAMP data type plus assciated DAY(), MONTH(), DAYOFWEEK() functions.
The only valid reason for separate DAY or HOUR columns in a separate table is if you have procomputed totals and averages for each timeslot.
Even then its only worth it if you expect a lot of filtering based on these values, as the cost of building these tables is high, and, for most queries the standard SQL "GROUP BY ... HAVING .. " will perform well enough.
It sounds like you may be interested in a "STAR SCHEMA" wikipedia a common method in data warehosing to speed up queries -- but be warned designing and building a Star Schem is not a trivial exercise.
James Anderson
2010-09-13 01:17:37
My only concern is this: I am showing users graphs for say number of wall posts in June further filter by number of post over the weekend (which they will input what a weekend is as it varies across the world). Then users can compare previous months weekends and previous years weekends. This is an example only. With such a business requirement what would be a better approach to store the day. Even time will be filterable but for system usage so we can track what time of the day people in say UK are active the mot with sharing status vs sharing photos, etc...
kino
2010-09-13 02:13:41
My understanding then is I will require a Fact table build around User Activities, then I can add various dimensions around it like Time, Location (which has again in depth filters like continent country state, city, neighborhood.. the people who view the updates, etc are from), then Identity (Age, nationality, sex,...).
kino
2010-09-13 02:18:56
You can still do all this with "normal" sql queries on a Timestamp/date time field. But in your case I think you would be justified in storing at least year and month as a single coulmn YYMM,day of week and hour in separate columns which could be indexed to speed up your queries. Hint on most DBs it would be a good idea to have an index 'month,day,hour', an index 'day,month,hour' and another on 'hour,day,month'.
James Anderson
2010-09-13 06:51:05