MySQL field management

Question

When I make a query, I often wonder about this:

Is it better to add extra field(s) to the table, or just get the values and calculate in your server side language?

For example, table Student contains Student.score field. I want to get the grades.

Do I get the score and make a bunch of if/else/switch for the grades?

Do I waste space by adding a field just to store something that is dependent on another field?

The former is annoying to do everytime you need the grades. The latter scares me when I think of space usage. If I have as small as 10,000 records then I've already wasted 10,000 fields!

Which is the most efficient method, considering non-trivial tables and medium to big database size?

If it matters lets use PHP as the server side language.

Answer 1

Your data persistence layer should generally not store calculated values that are subject to frequent change. To do so creates the risk of allowing stagnant data to persist within the data store and then affect the business layer which will in turn affect everything else.

Generally speaking you always want to produce calculated values at run-time.

However, I wouldn't worry about a single additional field within the database when determining the best way to address any design issue. Hard drive space is both cheap and plentiful. :-)

Answer 2

When you add the extra field, you also need to consider that the extra fields will also need to be updated whenever the field it depends on is updated. Is it possible to move the calculation into the SQL instead of looping through the record set and doing it in the server-side code?

Answer 3

Your DB is a lot more powerful than you give it credit for.

A lot of this depends on your DB type and versions but there are procedures and functions you can use to make your scripts more efficient. These methods can be simple quick calls compared to the multi-line solutions server-side languages present. Why not make a procedure that--when called--returns all of the student's info plus a letter grade that it figured out for you? Why not use some of the math functions to find the difference between two fields? Why not use datetime functions to format your date fields?

There's a lot of ways you can use your DB to process information. It just depends on the size you expect your database to be. If it's just a simple 5 page CMS web site then, no, you probably don't want too much of the work done on the DB's side but, if you have a large database with multiple high-volume tables that have relationships with one another then, yes, you probably want to move some of your processing over to the DB.