3

For instance, say I have a data structure with two integer columns start and end, but I'm actually interested in the start and difference between start and end values.

I could send this query:

SELECT start, end - start FROM foo;

or, I could just do

SELECT start, end FROM foo;

and perform the $end - $start operation in PHP. Is it better to leave these sorts of simple operations to MySQL?

Improve this question
2
  • 6 of one half a dozen of the other, or just benchmark it your self. Commented Aug 15, 2011 at 20:30
  • 1
    I'd let MySQL do it. You could also alias the expression: SELECT start, end - start AS delta FROM foo; Commented Aug 15, 2011 at 20:32

5 Answers 5

Reset to default
5

I would classify "$end - $start" as business logic, and that belongs in the model layer not the persistence layer. That means performing the calculation in PHP. This has a number of benefits:

  • If you change databases later, you don't need the same operators to exist.

  • You can source control the logic that performs the calculation.

  • You can more thoroughly unit test.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Finally someone with some sense :)
This is one of the arguments that's used eternally to justify writing "database independent" SQL. Of course that sort of sql ends up being lowest common denominator. If you make a commercial product where you absolutely have to provide database independence, then this is an issue, but in my 20+ years of system engineering I have never been involved in a project where the database backend was changed. It's a rare requirement and not a good argument. MySQL will do the arithmetic faster and with less overhead, because the memory in the server is shared.
Additionally, I don't know of a sql database that doesn't let you do basic arithmetic on 2 columns in the row.
Whether or not you can isn't the point. If the calculation is considered business logic, it should be in the logic layer, not the data persistence layer. The question then becomes, is this simple calculation considered business logic? In this case, it's hard to say without more detail. But once you consider the example where you want to make a non-trivial adjustment to the calculation (for example, if the numbers are timestamps and you want to exclude weekends), then you're undeniably in the logic layer, and if you had started there in the first place, the change would be less painful.
1

I'm one of those database should do data things so unless you're using an aggregate function most things like this should be done on the application end. Now for simplicity here your query could be just

SELECT start, (end - start) AS difference FROM foo;

but for more complicated things it's recommended that you do a lot of the data manipulation in the application side.

The other answer says to put the logic as close to the DB as possible (which IMO is a horrid idea) although in this case I see nothing wrong with what you're doing but just remember that the database is already doing a lot of work to just get you the data, if you're going to be doing a lot of logic on the database it's going to make it just that much slower. For small applications it's not a big deal but for large scale applications you will see implications very quickly not only in performance but adding new functionality (splitting logic between database and application level gets very confusing and hard to refactor).

Improve this answer

Comments

1

Normally any view transformation should be handled in presentation layer. In your case your model is designed with start & end fields, so you should expose this fields from sql.

Improve this answer

Comments

1

MySQL can perform some basic arithmetic. However, it is definitely better to perform that sort of arithmetic in PHP if you're already using the data in PHP anyway.

MySQL is meant more for simply storing and extracting relational data.

Improve this answer

Comments

0

For simple operations I would use MYSQL. I'm not a guru, but I like to keep things as simple and clean as posible so where it is possible I use MYSQL built-in functions to do the stuff that can be done with php. In other words - using MYSQL would lower the amount of code and formatting necessary to display the data.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.