I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance table, but wondered about summarizing the data and if that might be faster/better. So, I've conisdered a table that looks something like:
create table stu_summary ( id int identity primary key nonclustered not null, stu_id char(10) not null primary key references student (stu_id), group_name varchar(50) not null, item_name varchar(50), value varchar(100))
which would then allow me to have data that looked like:
1, 'abc112233', 'tardy-count', '2011-F-21-99383', 2
which would signify that this record shows that student abc112233 has 2 tardies for the academic year 2011 in fall for progress period 21 and class id 99383.
Is there a better way to store this kind of info? I'm hoping to keep the table flexible and not tie it down to the specific data columns. Am I headed in a bad direction? What have you done to store this kind of date span specific summaries?
There has been speculation as to the amount of data, number of tables in question here, so let me illuminate:
- There are 70k rows of attendance recorded each day, 1.2m per month
- Each student's tardy count will be displayed in an attendance window with a grid. This is important because a student normally has 6 classes and so their attendance will be loaded frequently throughout the day
- The attendance table has a
codecolumn in it that determines what kind of attendance it is. The tardy code is one of those values.