more table or more rows better in sql?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

1) 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 column like ac_year) , or should i make separate tables for each year. Also that there are to be different tables to store information like, classes, marks, fee, hostel, etc about the students. So each Info, like Fee would be store 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

Posted 28-Feb-13 3:09am

Ag_Sharad

Updated 28-Feb-13 3:16am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Accepted Answer · 2013-02-28T03:25:00

Solution 1

The quick and not great answer is: A little of this, and a little of that.
Both methods are valid solutions. I personally don't like the idea of having an application built that will require structural updates for each year, so I would lean towards a two table system where you have one "live" data table where current data is stored and a single "archive" table where all out-of-date data gets moved to using an automated SSIS process.

Posted 28-Feb-13 3:25am

fjdiewornncalwe

Comments

Ag_Sharad 28-Feb-13 9:28am

u are right, but the thing is that i have separate tables for each information category like, Fee, hostel...... So i would have to keep an Archive table and a current table for all the different categories(tables)

gvprabu 28-Feb-13 10:28am

Hi,
If Data are same category means, Go for single Table.
In case if you will go for different tables then based on category you need to change the JOIN Condition in all SPs. So based on your Data you will take a right decision.