Let me define a term - schema refers to the structure of your database (tables, columns, keys) as opposed to the data in it (rows). Static schema refers to that structure never changing. A dynamic schema is one that changes as you add data. In most cases, the textbook (correct) approach uses a static schema.
Some cases work out well with a dynamic schema. Historical datalogging, for example, where your “data” is a (tag value, timestamp) pair, and you have lots of different tags that all must logged at the same interval.
The simplest dragging and dropping of groups in FactorySQL is another dynamic schema example. These groups work best for 2 levels of data - device (row) and property on that device (column). The point is that it’s easy to use and easy for non-programmers to create queries against. So if we had 100 gizmos with 20 I/O points, we’d have one table with 100 rows and 20 columns. The real advantage is that it would be trivially simple to create one control screen in FactorySQL that would work with any of the 100 gizmos by changing the WHERE clause of our query. Further we could add a 101st gizmo to the database without touching our FactoryPMI application and it would show up.
Suppose your application has many layers deep, all of which are subject to change. You need to go with a static schema approach. Let’s consider a downtime system that we wish to incorporate on all plants. The levels of data may look like this:
Plant->Line->Production Run->Equipment->Downtime Event
For this example, you would use a static schema with 5 “levels” of data.You would want a static schema with a table for: plants, lines, runs, equipment, downtime. Each table would include a column that points to the instance of the table above it. The layout and queries are more complex than a “naive” dynamic schema layout, but in the end you’ll have an application that scales far better. If you set it up properly, then anytime you add anything in FactorySQL (adds new row to one of those tables) will show up in your FactoryPMI project.
To implement a static schema in FactorySQL, you use block data groups. Stay tuned for SQLTags. This next major release feature will provide a static schema approach in the backend that you won’t have to think about.