wood burning stoves 2.0*
The moose likes JDBC and the fly likes What's the difference - one table, or multiple tables? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What Watch "What New topic
Author

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

Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
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

Joined: Oct 23, 2002
Posts: 904
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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Oct 23, 2002
Posts: 904
Thanks Paul for your comments.. I'll remember that.

/Svend Rost
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What's the difference - one table, or multiple tables?