Table structure for creating timetable for a school

Question

Post reply

Table structure for creating timetable for a school

priyanka.ks

Old Hand

Points: 388
More actions
January 24, 2015 at 12:05 am

#314060

Hi All,
I am working on creating a timetable app for school. I have been given a requirement as below
1. The weekdays like Monday,Tuesday,...should be displayed on top
2. Timing slot would be 8:00, 9:00,... which would be displayed vertically
the table would display as below
MondayTuesday WednesdayThursdayFriday Saturday
07:00Physics Chemistry Maths BiologySocialScience Craft
08:00MathsValueEducation Chemistry EnglishBiology Yoga
09:00GamesComputerScience Science Chemistry Hindi Maths
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
Timings MondayTuesday WednesdayThursday Friday Saturday
07:00Physics Chemistry Physics Biology SocialScience Craft
08:00MathsValueEducation Chemistry English Biology Yoga
09:00GamesComputerScience Science Chemistry Hindi Physics
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
Please help me with this db structure. should the structure be mentioned in the same table or assign a different table for difference data.
Let me know if any further clarification required.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63436 More actions · Answer 1

Off the top of my head, something like this would work:

CREATE TABLE Course (

CourseID INT IDENTITY(1,1)

,CourseName VARCHAR(20) NOT NULL

);

GO

INSERT INTO Course (CourseName) VALUES ('Physics'),('Chemistry'),('Maths'),('Biology'),('SocialScience')

,('Craft'),('ValueEducation'),('Yoga'),('Games'),('ComputerScience'),('Science'),('Hindi');

/*

MondayTuesdayWednesdayThursdayFridaySaturday

07:00PhysicsChemistryMathsBiologySocialScienceCraft

08:00MathsValueEducationChemistry EnglishBiologyYoga

09:00GamesComputerScience ScienceChemistry HindiMaths

*/

CREATE TABLE Schedule (

DayNumber TINYINT

, Period TINYINT

, CourseID INT

CONSTRAINT pkSchedule PRIMARY KEY (DayNumber,Period))

Then build a report in SSRS - just much easier to deal with that way... One axis is DayOfWeek, the other is TimeSlot.

priyanka.ks Old Hand Points: 388 More actions · Answer 2

Thanks for your reply.

I wanted to know how would I deal with my 3rd requirement wherein for a particular slot on a particular day the course might get changed and I when i view for that particular day, it should show me the changes that have occurred.

pietlinden SSC Guru Points: 63436 More actions · Answer 3

suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.

.

There's a difference between a schedule and what happens. A schedule is a template for what should happen. So if you wanted to change an instance of a class (say History on 24/01/2015), then you would have to "explode" the calendar so that it described specific dates. Then you would update a record in that resulting table for a given time period.

Once you explode your schedule, it would be something like:

ExplodedSchedule(

SchoolDate DATE,

TimeSlot TINYINT,

RoomNumber TINYINT,

CourseID TINYINT

PRIMARY KEY (SchoolDate, TimeSlot, RoomNumber));

Then you could easily change that.

UPDATE ExplodedSchedule

SET CourseID = 12

WHERE SchoolDate = '2015-Jan-24'

AND TimeSlot = 2;

If you wanted to keep the original record somewhere, you would have to insert it into some other table, by either incorporating that in the stored procedure to do the update or in a trigger (except triggers can get messy!)

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 4

Nah. Done correctly, audit triggers are fast and a breeze to write. It also depends a bit on whether you're doing "whole row" auditing or "column level" auditing, the later being the more complicated of the two.

The key to such audits is to never audit INSERTs, which would automatically double the size of the data... one row for the trigger table and one row for the audit table.

In an audit system, only the "old" data should be in the audit table and whatever the "new" data is will be in the trigger table (the table with the trigger on it). If a row is inserted and never updated, that row won't show up in the audit table. The trigger table becomes a part of the "audit".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182889 More actions · Answer 5

Quick thought, the problem doesn't require a complex table structure, something like this should do

😎

+----------+ +---------------+

| Calendar |-+--------, | ClassSchedule |

+----------+ | +---------------+

+----------+ '--+<| DateTime |

| Room |+------------+<| Room |

+----------+ ,--------+<| Teacher |

+----------+ | ,-----+<| Class |

| Teacher |-+--' | ,--+<| Subject |

+----------+ | | +---------------+

+----------+ | |

| Class |-+-----' |

+----------+ |

| Subject |-+--------'

+----------+

priyanka.ks Old Hand Points: 388 More actions · Answer 6

Hi Eirikur,

Thank you for your suggestion. I find it ideal. I might tweak it a bit to fit my requirement. I would come back again for the help when needed.

Thanks all.

MMartin1 One Orange Chip Points: 27786 More actions · Answer 7

Using tables like a Calendar, Course, TimeOfDay table (separate from Calendar which implies a day as the primary key) or Period ...

you could employ a simple snapshot table that populates once every term to view what is the 'norm.' You could include the term column in the calendar table also for convenience in other areas (ex//. 'Winter2014' ).

Any deviations from that Calendar can be included into a table using the design Eirikur suggests. That way you know only deviations from the norm are in this transactions type table.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 8

MMartin1 (2/17/2015)
Using tables like a Calendar, Course, TimeOfDay table (separate from Calendar which implies a day as the primary key) or Period ...
you could employ a simple snapshot table that populates once every term to view what is the 'norm.' You could include the term column in the calendar table also for convenience in other areas (ex//. 'Winter2014' ).
Any deviations from that Calendar can be included into a table using the design Eirikur suggests. That way you know only deviations from the norm are in this transactions type table.

If you include start and end dates, then you can set it up as a Type II slowly changing dimension and retain any history.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27786 More actions · Answer 9

Jeff Moden (2/17/2015)
MMartin1 (2/17/2015)
Using tables like a Calendar, Course, TimeOfDay table (separate from Calendar which implies a day as the primary key) or Period ...
you could employ a simple snapshot table that populates once every term to view what is the 'norm.' You could include the term column in the calendar table also for convenience in other areas (ex//. 'Winter2014' ).
Any deviations from that Calendar can be included into a table using the design Eirikur suggests. That way you know only deviations from the norm are in this transactions type table.
If you include start and end dates, then you can set it up as a Type II slowly changing dimension and retain any history.

Right, I thought about that scenario, I just figured that in this case here if the change is not permanent (ie.. once the sick teacher comes back, the regular course will take effect again) then it would be better to leave the intended schedule as a snapshot and any minor changes in a transactions table.

You could add a type 2 SCD if indeed there was a change that is known to be permanent as far as the eye can see to the snapshot. So that would make this a rather special design with another extra table to satisfy the need. I sure hope confusion would not rise too high :hehe:

----------------------------------------------------