A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I'm writing an app that needs to track facility utilization. The facility is open on a different schedule weekdays and weekends and seasonally is closed on Sunday. Its schedule changes from season to season.
Events of variable duration are scheduled in the facility on given days and in different rooms. Any events whcih last more than a single calendar day's duration are scheduled as two separate events of the same class.
I'll need to create reports that show the number of hours and % utilization for each room over variable periods of time (weekly, monthly, quarterly, yearly, user-entered, etc.).
Creating the table to store events is easy. My question is, what have you found to be the best for storing and retrieving the facility operating hours?
One thought is to have a table with a SeasonStartTimestamp and a SeasonEndTimestamp, with MondayOpen, MondayClose, Tuesday... through the week. I could then create a view that turns this table logically into a table of dates with open and close times per date, then use the view as a table. Has anyone used this approach, and/or am I making things too complex?
Another obvious approach, of course is to create a table that directly stores Date, OpenTime, CloseTime and then build a system to populate the table based on user-input Season start/end dates and differential open/close times for each weekday over the season. In other words, build the logical View I described above as a physical table.
Your thoughts on which of the above is a better approach, or whether there is a third (or fourth), better approach?
Thinking further about this, it may not be possible to create a view to do what I want. Given this:
which will create one row to indicate a "season" with a begin and end date, and the hours the facility is open each weekday during the season ...
Can I create a view that takes the output of a SELECT INTO #TempTable command, where that SELECT command builds a multi-row dataset from a single row table?
I'd normalize your season table:
Season: (season id, start date, end date)
SeasonDayOfWeek: (season id, day of week, open hour, close hour)
If you populate a date table:
Date: (date, day of week)
then you can
select date, open hour, close hour from Season s, SeasonDayOfWeek w, Date d where date is between start_date and end_date and s.season_id = w.season_id and d.day_of_week = w.day_of_week
Sunday, February 24, 2008
Mike, thanks for the advice.
I decided that I really can't use a view to create more records from a table than already exist, so I created a sproc that builds the table of OpenDateTime / CloseTime records on the fly into a permanent/temporary table, from the table of SeasonID / MonOpen / MonClose etc.
I'm not sure if normalizing the day-of-week records really buys me anything in this context - at any rate it's already done.
This set of records will only ever be used to create reports - and then will be discarded - so I figured that a temptable is probably the best way to go.
I would seriously consider a permanent table of all the days (it's only 365 records per year), and then link them to different statuses either with attributes in the table or if many-to-many with a link table to different categories. This type of technique is often used in dimensional data warehouses and, for reporting, the technique is particularly relevant.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz