Five design decisions when moving PLC data into SQL

Writing PLC data into an SQL database can be done in an afternoon with good tools: a source, a target, a flow. The real question is whether it will still be correct, fast and queryable two years later. The five decisions below, taken before the first flow is built, avoid expensive rewrites later. The tool that moves the data may be OPC Router or the Kepware Server DataLogger; the decisions are the same.

Decision 1: When to write?

• Periodic: a sample every N seconds. Simple for trends and journals, but volume grows quickly.
• On change: when the value crosses a deadband. It cuts volume noticeably, but for the question “why is there no data?” an extra heartbeat (periodic record) may be needed.
• On event or at batch end: production result, counter value, batch summary. The cleanest option for reports.

An example calculation: 500 tags × 1 record per second = roughly 43.2 million rows a day. Recording on change with suitable deadbands reduces this volume noticeably in most processes.

Decision 2: Table structure

For continuous measurements, the narrow structure is a sound start. The example below is only to give an idea; syntax varies by database engine:

CREATE TABLE tag (
  tag_id      INTEGER PRIMARY KEY,
  path        VARCHAR(200) NOT NULL UNIQUE,  -- OPC item path
  unit        VARCHAR(20),
  description VARCHAR(200)
);
CREATE TABLE measurement (
  tag_id     INTEGER   NOT NULL REFERENCES tag(tag_id),
  source_ts  TIMESTAMP NOT NULL,             -- UTC, source timestamp
  value_num  DOUBLE PRECISION,               -- NULL when Bad
  quality    SMALLINT  NOT NULL,             -- 0 Good, 1 Uncertain, 2 Bad
  PRIMARY KEY (tag_id, source_ts)
);

Decision 3: Time

• Store UTC and convert to local for display; daylight saving then does not produce duplicate or missing hours.
• Write the source timestamp, not the write moment of the server or database (data quality and timestamps).
• Keep PLC, server and database clocks synchronized with NTP.
• A primary key or index on (tag_id, source_ts) is the foundation of time-range queries.

Decision 4: Quality and empty values

Do not write a value of Bad quality as zero; it cannot be told apart from a real zero. Leave the value NULL and record the quality code. Reports should filter by quality or show the gap explicitly. This underpins the accuracy of OEE, consumption and average calculations (preparing OEE data).

Decision 5: Errors, volume and retention

• Errors: if the database is unreachable, data must not be lost; it should be buffered at the source and resent when the link returns (redundancy, buffering and recovery).
• Duplicates: resending must not produce the same record twice. Use a write that ignores or updates on a primary-key conflict (upsert).
• Batch writes: group records over short intervals and write them in one transaction instead of one by one.
• Retention: how long will you keep raw data? Reduce old data to hourly/daily summaries and archive it; consider time-based partitioning for large tables.
• Separate views for reporting: reports should not query the raw table directly; use summary tables or views.

SQL or historian?

SQL suits medium-rate data, relational queries and enterprise reporting. For very high-rate time series, compression and trend tools, a historian or time-series database may be more efficient. They are also used together: raw data in the historian, summaries and events in SQL. For all data collection options see choosing the right data collection path.

Frequently asked questions