I'm not sure if it's better that i can produce a new column during my Mysql database or otherwise.

I've got a table :

calculated_data (id, date, the_value, status)

status is really a boolean.

I want an additional value named : the_filtered_value

I'm able to have it easily such as this :

Choose IF(status Is Fake, , the_value) Because the_strained_value FROM calculated_data

The calculated_data table has countless records and that i display the_value and the_filtered_value in charts and data tables (using php).


Is it more beneficial to produce a new column the_filtered_value within the calculated_data table or simply make use of the SELECT IF query?

In "better" I see :

  • better in performance
  • better in DB design
  • simpler to keep
  • ...

Interesting help!

Don't give a column. Rather, produce a VIEW in line with the original data table as well as in the vista give a "virtual" calculated column known as the the_strained_value according to your expression.

In by doing this you'll have easy accessibility strained value without needing to copy the "logic" of this expression to various places inside your code, yet still time not storing any derived data. Additionally, you'll have the ability to operate on the vista as though it were a table in many conditions.

 CREATE VIEW calculated_data_ex (id, date, the_value, status, the_filtered_value)
   AS SELECT id, date, the_value, status, IF(status IS FALSE, 0, the_value)
      FROM calculated_data

Adding the additional area adds complexity for your application but make queries simpler (specifically when became a member of on other tables).

Personally, i always keep the information as separated as you possibly can around the database and that i handle this cases on my small application. Utilizing a MVC pattern makes this simpler.

This works in MS SQL but I don't determine if MySQL will offer the syntax.

declare @deleteme table (value int, flag bit)
insert @deleteme
Values
(1,'False')
,(2,'true')

Select *, (flag*value) AS the_filtered_value from @deleteme