I am working on converting an existing reporting program that is using Wonderware and MSAcess. Currently WW is writing rows to a table in the database and there are a few forms with querys for shift reports. We are going to convert this Ignition and MySql.
Right now data is logged every minute. The shift reports are based hourly. Would it be better to keep this same practice of logging every minute and then building another table with hourly averages and totals for the report? Or would it be better to just skip that step and just have Ignition calculate the values every hour and only log that value?
Just looking for suggestions on how to go about this project.
It depends What’s the relevant data? How long do you need to keep the relevant data? How long do you have to keep the raw data? How big will your raw data table be? What happens if there’s an error in the raw data?
When I start on a database schema I ask the question, the customer what’s to know ‘what’ and for how long? Then I create the raw data table to answer ‘what’. Now the answers to the above questions help determine if I will create a summary table or not. Is the time period for the relevant data the same as the raw data? If yes then no summary table. Will the raw data table be so large that there will be performance hit when a query for avg/totals is performed? If yes, then create a summary table. Will an error in the raw data affect subsequent data? If yes, then create a summary table. Is the summary data the only relevant data? If yes, then create a summary table.
That’s the mental steps I go through when determining if I’m going to create a summary table or not.
What’s the relevant data? How long do you need to keep the relevant data? How long do you have to keep the raw data? How big will your raw data table be? What happens if there’s an error in the raw data?