9

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?

Improve this question
3
  • "15 exam marks" - so like multiple choice of a single exam or the scores of 15 tests?
    – rfusca
    Commented Aug 31, 2012 at 16:46
  • scores of 15 tests
    – mike
    Commented Aug 31, 2012 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
    Commented Aug 31, 2012 at 18:13

3 Answers 3

Reset to default
27

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.

Improve this answer
2
  • 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
    Commented Aug 31, 2012 at 18:00
  • +1 Great further explanation from mine. I tend to be too concise lol.
    – rfusca
    Commented Aug 31, 2012 at 18:03
12

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.

Improve this answer
12
  • 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
    Commented Aug 31, 2012 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
    Commented Aug 31, 2012 at 17:20
  • 1
    fiddle here - sqlfiddle.com/#!1/f7343/10
    – rfusca
    Commented Aug 31, 2012 at 18:00
  • 6
    @EdwardDortland it'll always be 15 until it isn't.
    – therefromhere
    Commented Aug 31, 2012 at 21:17
  • 1
    @EdwardDortland: The calculations are fine. Now, can you do them for the indices you may need?
    – ypercubeᵀᴹ
    Commented Aug 31, 2012 at 23:41
1

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.

Improve this answer
4
  • 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ᵀᴹ
    Commented Aug 31, 2012 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 ♦
    Commented Aug 31, 2012 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
    Commented Aug 31, 2012 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
    Commented Dec 6, 2012 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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