more table or more rows better in sql?

Question

Actually i am building a software for academic institutions, so i just wanted to know answers of a few questions:

As you know the some new data will be generated each year(for new admissions) and some will be upgraded. So i should store all the data in one single table with academic year separation(as a columns like ac_year), or should i make separate tables for each year. Also that there are different tables to store information like, classes,marks,fee,hostel,etc about the students. So each Info, like Fee would be stored in different tables like Fee-2010 Fee-2011 Fee-2012...

Or in 1 single Fee table with year as a columns.

One more point is that soon after 1-2 years database will get heavier so backing up data for a single year would be possible in single table(Like Fee with year as a column) ?

And Please answer keeping in mind SQL Server 2005.

Thanks

Answer 1

As you phrase the question, the answer is clearly to store the data in one table, with the year (or date or other information) as a column. This is simply the right thing to do. You are dealing with the same entity over time.

The one exception would be when the fields are changing significantly from one year to the next. I doubt that is the case for your table.

If your database is really getting big, then you can look into partitioning the table by time. Each partition would be one year's worth of data. This would speed up queries that only need to access one year's worth. It also helps with backing up and restoring data. This may be the solution you are ultimately looking for.

"Really getting big" means at least millions of rows in the table. Even with a couple million rows in the table, most queries will probably run fine on decent hardware with appropriate indexes.

Answer 2

It is always better option to add new property to entity than create a new entity for every different property. This way maintenance and querying will be much more easier for you. On the performance part of querying you don't have to worry about internal affairs of data and database. If there become a real performance issue there are many solutions like Creating Index on years as in your situation.

Answer 3

It's not typical to store the data in multiple tables based on time constraints. I would prefer to store it all in one table. In the future, you may look to archiving old data, but it will still be significant time before performance will become an issue.