Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am developing a student tracking program where I need to store 15 exam marks.

I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical operations. However, I need as much performance as possible.

Which is better? A single string field, or 15 individual int fields?

share|improve this question
    
"15 exam marks" - so like multiple choice of a single exam or the scores of 15 tests? –  rfusca Aug 31 '12 at 16:46
    
scores of 15 tests –  mike Aug 31 '12 at 16:57
1  
Without more information about the type of database (traditional relational with indexing available?) and requirements for data access and usage patterns, it is difficult to say what design you should use and how it will perform. –  Cade Roux Aug 31 '12 at 18:13
add comment

3 Answers 3

If you are already talking about splitting and computing, don't store this as an array.

Regardless of the relational theory and traditional normalization rules and dogma, it's simply a design which gives you MINIMAL flexibility.

Make each exam result a row.

I'm not trying to anticipate everything, but there are a very large number of things which this more granular (and, yes, normalized) and only ever so slightly more space expensive design facilitates which you may or may not need now and may or may not need in the future:

  • Throwing out highest and lowest result? You will have to slice up your array and sort it.

  • Averaging? You will have to slice it up and total it

  • Analysis of exam result by exam across students? You'll have to slice and pivot

  • Sorting for counting (or instance British GCSEs, where it might be 7 As and 2Bs)? You'll have to slice and sort

Note that all this slicing and sorting comes very cheaply in an indexed, normalized design.

share|improve this answer
4  
Just what I was going to say but you said it better! Storing mulitiple values in one string is one of the worst possible design choices for any database. –  HLGEM Aug 31 '12 at 18:00
    
+1 Great further explanation from mine. I tend to be too concise lol. –  rfusca Aug 31 '12 at 18:03
add comment

For scores, performance-wise, the clear winner is storing it numerically something like this;

create table test_scores
(
  student_id int,
  test_id int,
  score int
);

Its easy to query, easy to update and add on, and super easy and fast to perform aggregates on. Given the choice of "store this information as a string that I have to split up" or "store in a column"...the winner is almost always going to be "store in a column" for most use cases in a RDBMS.

share|improve this answer
    
If it's always the same set of 15 exams, it could well be that storing them denormalized (15 columns) is faster to process. A question, did you purposely propose an integer data type? –  Edward Dortland Aug 31 '12 at 17:16
    
Additionally, for every 15 exams of 1 student your now storing 15 times a student ID and a test ID extra. –  Edward Dortland Aug 31 '12 at 17:20
1  
fiddle here - sqlfiddle.com/#!1/f7343/10 –  rfusca Aug 31 '12 at 18:00
6  
@EdwardDortland it'll always be 15 until it isn't. –  therefromhere Aug 31 '12 at 21:17
1  
@EdwardDortland: The calculations are fine. Now, can you do them for the indices you may need? –  ypercube Aug 31 '12 at 23:41
show 7 more comments

as long as you use tiny int (0 to 255) using a char(15) or 15 tinyint is the same (size wise). So then from a performance perspective, go for the 15 tinyints since you save on the extraction and string handling.

UPDATE

if the marks are double digits, you'll need CHAR(30) and that is twice the size of 15 times a tinyint.

share|improve this answer
    
Thank you for your helpful comment! –  mike Aug 31 '12 at 16:52
9  
Given this extremely simple design, if there's an institution on this planet that has enough students sitting 15 exams (with marks) to cause performance problems in a modern RDBMS, I'll cry myself to sleep tonight. –  Phil Aug 31 '12 at 17:37
1  
If the marks are double digits? But tiny int covers scores from 0 to 255, or -127 to 127 depending on how you prefer to count. So since scores rarely go negative, that gives 250+ points for one exam, and most exams are scored on a 0-100% scale. I think tinyint is absolutely useful here. –  jcolebrand Aug 31 '12 at 18:58
    
Yes we agree, I was simple stating that with double digit marks as opossed to single digit marks it becomes even worse to store it as char. Since then you would need char(30) instead of char(15). While double digit or not, 15 tiny ints will always be just 15 bytes. –  Edward Dortland Aug 31 '12 at 19:40
    
-1 because this answer recommends the fields per row design that is far inferior to the storing of each exam result in its own row as proposed by the other posts –  miracle173 Dec 6 '12 at 6:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.