Should I create a new DB column or not?

I don't know if it is better for me to create a new column in my Mysql database or not.

I have a table :

calculated_data (id, date, the_value, status)开发者_运维百科

status is a boolean.

I need an extra value named : the_filtered_value

I can get it easily like this :

SELECT IF(status IS FALSE, 0, the_value) AS the_filtered_value FROM calculated_data

The calculated_data table has millions of entries and I display the_value and the_filtered_value in charts and data tables (using php).

Is it better to create a new column the_filtered_value in the calculated_data table or just use the SELECT IF query?

In "better" I see :

• better in performance
• better in DB design
• easier to maintain
• ...

Thanks for your help!

Do not add a column. Instead, create a VIEW based on the original data table and in the view add a "virtual" calculated column called the the_filtered_value based on your expression.

In this way you will have easy access to the filtered value without having to copy the "logic" of that expression to different places in your code, while at the same time not storing any derived data. In addition, you will be able to operate directly on the view as if it were a table in most circumstances.

 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 extra field adds complexity to your app but make queries easier (specially when joined on other tables).

I personally always try to keep the data as separated as possible on the database and I handle this cases on my application. Using a MVC pattern makes this task easier.

This works in MS SQL but I do not know if MySQL will support 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