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

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)

Answer 2

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 3

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 4

I would suggest that there is no need to split this table up. If you create appropriate indexes for any selective queries you may need to perform, the system should be able to find the required rows very quickly. Even for analytic queries that involve all rows, 2 million such records should only require a second or two to scan, which I imagine would not present a great problem.

MySQL now also supports partitioning of data as an optional feature. Partitioning is similar to your proposal to split the table up, but it is done at the physical level, so it isn't visible to users or developers using your schema. This may be a useful approach if you find that a single-table implementation is still too slow. This document provides an overview of partitioning in MySQL 5.4.

Answer 5

Checksum,

I echo Michiel opinin that this is premature optimization.

What you can basically do later on to improve performance is use the database archiving and partitioning features so that your database reads are efficient. I can sugest creating index on this table also. Anyways I do not believe 1 million records is huge. Databases today are capable of handling such big numbers. Also you will encounter the performance problems 3 years form now only

So go ahead write code rather than thinking of what go wrong!