Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Edit
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 code column in it that determines what kind of attendance it is. The tardy code is one of those values.
share|improve this question
2  
why would you abbreviate (stu_summary, stu_id)? Aren't student_summary and student_id a lot more natural to type, read etc.? I know that isn't the question but you and others are going to be reading and writing code around this design for some time. –  Aaron Bertrand Sep 7 '11 at 20:36
    
Your suggestion is certainly the better way to go, however, this is the current naming convention –  Nick DeVore Sep 7 '11 at 21:20
add comment

2 Answers

up vote 3 down vote accepted

Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap. How many rows do you expect there to be in the long run in your students or attendance tables? Probably on the order of tens of thousands at most. Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY 
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.

share|improve this answer
2  
+1 definitely agree. Maintaining this in a separate table just means you have to introduce your own triggers or have a background job that is constantly updating the other table and keeping it in sync. The indexed view is guaranteed to be in sync with the table. –  Aaron Bertrand Sep 7 '11 at 20:37
    
Is there somewhere you can point me to possible performance issues? Perhaps there aren't any noticeable perf hits? –  Nick DeVore Sep 7 '11 at 21:37
    
@Nick - Performance hits using an indexed view? Indexed views will greatly speed up any queries that request the data it aggregates. Data changes that affect the indexed view (e.g. inserts to the attendance table) will incur a small cost since the view will have to be updated. Read the last link in my answer for the full skinny on indexed views. –  Nick Chammas Sep 7 '11 at 21:50
add comment

You should avoid doing this kind of denormalization/summarization on any data that has a fair risk of being modified. If you have huge mountains of transactional data that must be queried many, many times and if the transactions are read-only in the timeframe that you are summarizing, then building a summary table like you are considering isn't too bad.

However, you have to be prepared to live with the risk of your summary table being out of synch with your raw transactions. This means either living with the down-sides of an indexed view, or manually building (and maintaining) re-synching logic, or being prepared to suffer through inconsistent data - or a combination of these.

In large transactional systems you sometimes need to bite the bullet and build summary tables for reporting. I would suggest you consider carefully whether your specific application example requires this or whether on-the-fly reporting (or even an indexed view) is a better way to go.

share|improve this answer
    
I appreciate your input. Does the additional information I provided equal "many, many times"? –  Nick DeVore Sep 7 '11 at 21:35
3  
That is a good amount of data. The real question is how often is the data actually queried versus how often is it written? Does the tardy count come up every time a student properties page is displayed by a teacher or office staff member? Does that amount to a lot of views? I would look at profiling the query that calculates the figures on the fly and see how much load and/or response time it is adding. If either of those is proving to be too expensive, then you have a pretty decent case for building a pre-calculated snapshot table - as long as a snapshot will meet your business needs. –  Joel Brown Sep 7 '11 at 22:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.