Design for the questions you need to answer
A shift report and second-by-second process analysis run different queries. Define the questions first, then design tables and indexes. A starting record can include equipment identifier, measurement identifier, source time, ingestion time, value, unit and quality. Storing numeric values as text makes sorting and calculation harder. Creating a separate table for every tag can also make schema management unnecessarily complex. Use representative reporting queries to evaluate a design rather than judging it only by how easy the first insert is.
Preserve time and quality
Because networks introduce delay, arrival time is not measurement time. Keep both so consumers can calculate data age. Store UTC and display local time to reduce ambiguity in reports across plants. Carry bad or uncertain quality codes with the record instead of deleting them. Do not repeatedly record the last value as a valid new measurement when a sensor is offline. Missing data, an unchanged value and a genuine zero are different states with different operational meanings.
Design for replay
The same event may arrive again after an outage. For event data, use a source event identifier or a defined composite key to enforce uniqueness. A timestamp alone is not always unique. Use parameterised queries and restrict the connection account to the required table operations. Measure batch sizes with a representative workload; one enormous transaction may increase latency and lock duration. Decide how corrected measurements are represented so later changes do not erase the original evidence.
Make retention cost visible
Convert the incoming rate into daily row counts and measure storage growth including indexes. Decide how long raw records remain, when they are aggregated and how backups are restored. Test an index suited to frequent equipment-and-time queries; indexing every field increases write costs. Acceptance testing should measure report response time while production writes continue, not only peak insert speed. Include a restore exercise and a retention job check before declaring the database ready for operations.
Technical references
Check manufacturer documentation for product capabilities. Project steps and examples were prepared by OPC Turkey.