What's the difference - one table, or multiple tables?

Hi..

I've got a general question:

Im making a table with students - each student has a name, age, gpa
and class (ie. 1,2, .. , 7).

Should I make 1 table with everything, or 7 tables (1 per class)

Note that 1 student exists in 1 class.

pros and cons?

Thanks in advance

/Svend

My own guess is 7 tables, because they'll be smaller than 1 big one.
[ November 07, 2005: Message edited by: Svend Rost ]

One table. There are not seven different types of student, so you don't need seven different tables. Early optimizations are the root of all evil.

If I get your problem right, you are trying to capture attributes of student, class number being one of them, that can range between 1 to 7.
You would need one table for student, one table for class, and class id as foreign key in student table since one student can belong to only one class
Also class table will have 7 rows with one entry describing and identifying each class
Padma

Thanks for the replies..

@ Paul: I like your quote

@ Padme: Thanks for the reply. I dont think that would be a good idea, unless
"a class" has more infor than just a number. But perhabs I should brush up on
my normalization skills..

I used the "students" to illustrate my problem.. at first I wanted to use
7 tables, because each "class" didn't have anything in common, and since
the tables would be rather small (compared to one big DB) operations
would be faster. I did however choose to combine the 7 classes in one
(with a class attribute added), because it should be possible to get the
top x best students (i.e. highest GPA).

/Svend Rost

and since the tables would be rather small (compared to one big DB) operations would be faster.

Having a big table with lots of data doesn't necessarily mean performance will be poor - there are a whole bunch of things you can do to in a relational database to help such operations be performant. Databases are after all meant to be scalable; it would be a poor database implementation that required you to remodel your data once it started to get kind of big.

However, having queries that span seven tables, that is more likely to hit performance than a query that scans one. Your over-riding driver in data modelling is never performance. RDBMS's have been refined over the years to work well with proper normalization. Stick toi that and your should be OK. Second guessing what the DB might be up to in your model will end in tears.

Thanks Paul for your comments.. I'll remember that.

/Svend Rost