This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Svend Rost
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Padma Lalwani
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Svend Rost
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Svend Rost
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul for your comments.. I'll remember that.

/Svend Rost
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic