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 :
I'm able to have it easily such as this :
Choose IF(status Is Fake, , the_value) Because the_strained_value FROM calculated_data
calculated_data table has countless records and that i display
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
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