pivot table plus more issues - need help with denormalizing data
Posted on 2007-11-24
I am building course calendar for some school. Everything is done except for the actual display of the calendar. I have to work on existing database structure - meaning - I can add my tables and stored procs but can not change the ones that already exist. On top of that - calendar display needs to be in the form of pivoted table (html table at that) that has the columns set to dates with subsets of time frames (morning, afternoon and evening) and rows set to class room numbers.
The challenge lays in the way that data is stored and in the display format of calendar (pivot table).
Data is stored as follows:
course_id, section_id, semester, class_room, start_date, end_date, number_of_sessions, session_start_time, session_end_time, Mon, Tue, Wed, Thu, Fri, Sat, Sun
- Mon through Sun - contain the value set to 1 if class happens on that day of the week
- session_start_time and session_end_time are the start and end times for classes (ex. 8:00AM - 6:00PM)
My idea is to create another table based on existing date I described above that will have the following data in it:
course_id, section_id, semester, class_room, session_date, lesson_number, session_start_time, session_end_time, time_frame
the problem I have is with mapping data from one table to another:
- session_date - needs to be a date calculated based on start_date, number_of_sessions or end_date, and Mon trough Sun values (if 1 then number of sessions advances)
- lesson_number - i guess that would be the advancing session number from calculation of session_date
- time_frame - well - there I was planing to put morning, afternoon, evening, morning_afternoon, afternoon_evening, all_day - that is based on the session_start_time and session_end_time. Time frames would be timed 9:00AM - 12:00PM, 12:00PM - 5:00PM, 5:00PM - 10:00PM ( session_start and end times could be something like 8:30AM - 6:30PM - so they do not correspond exactly to time frame intervals but fall within one of the intervals defined above.
So - how do I map that data and display it in the format I want?
I am out of ideas and would give 1 000 000 points if I only could to expert who helps me with that.
Ideally - I would like to see as much ready solution as possible - but I will really appreciate any pointers I can get.
Thanks in advance