This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds and have James Denton on-line!
See this thread for details.
Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

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

 
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 ]
 
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.
 
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!