I was wondering where to do/put calculation of fields to be calculated from other data. Yes, I am talking about computational field. Also the computed data needs to be saved in database. So.. Weather it should be done at Database or by some external code like a windows service. ?
Let me put my need/scenario:
- I am developing a web application to disseminate data received by multiple instruments/sensors deployed somewhere in field.
- A windows service in C# is developed and deployed at Microsoft Windows SQL Sever 2008 R2 to receive, format and insert data into a table (Let say tblSensorData) in SQL database deployed at same machine.
- A Insert and Update trigger is implemented on 'tblSensorData' and is used to call a stored procedure which computes 3 other numerical values from received sensor reading and insert a row in another table (Let say 'tblComputedFields').
- Therefore, every new raw data received have a computed field row generated in database and hence I have consistency in this way.
I did some search on good or bad practice of dealing with computational fields and people argue to do at applications and some at database. But couldn't find a precise answer depending upon my current scenario.
One thing is for sure, if we do the same task of computation aside from database in any external code it should be Insert Event/Trigger based to know when to do the calculations and for consistency of data in database.
Any suggestion would be appreciated about where do the same task outside database. ?