Database design for school attendance system

Question

I'm working on a project for a school where a particular module deals with attendance system. I'm using LAMP(PHP 5.2+ MYSQL 5+) stack for development. Now the school strength is around 1500 and total number of working days per year is around 250. Plus, I've to keep records for 5 years before it can be erased.

The table structure is

studentId varchar(12) 
date date
fn varchar(1) *forenoon*
af varchar(1) *afternoon*

If I simply use a single table, that means 1,875,000 records for a 5 year period. Now instead of such a humongous database, I considered making a table for each class (not section). So considering there are 12 classes, I'll have 12 tables, which means an average of 1,55,000 records per table which is manageable.

Is this the right way to do it? Or are there any better ways?

Answer 1

What you are doing is called premature optimization. This is a common mistake.

You are better of getting your database structure as close to reality and in future if there becomes a need for optimization or speed improvement you can always do that.

From experience and looking at your example the single table solution looks fine.

Answer 2

A couple of points.

• 2 million records is not a big table.
• having a separate table per class is definitely not normalized.

You haven't really provided enough information re links to other table and what else, if anything, this table will store. But you should be starting with 3NF for all tables and only changing that if you find performance problems.

Answer 3

As long as you indexed your table columns properly, there shouldn't be a big problem with the first table.

I would disagree with the idea of splitting it up into the 12 classes, because you have no guarantee that that is the way it is going to stay (classes added, classes merge, etc.).

Mucking up your database normalization for a perceived benefit of efficiency is something you should look at only for extreme circumstances (if ever)