Our ignition installation is in its infancy and I want to get the SQL database structure right at the start.
The factory is split into approximately 6 departments, with around 4 manufacturing cells per department, each containing a number of machines or other assets. Eventually, we will be aquiring data from most of the assets.
I currently have two schema, one is used as a historian (ignition tag historian) and the other is used for storing batch data for use in various reports.
Are there any recommendations for a good database structure for the setup above? Should I have a schema for each manufacturing cell and set this as its own historical data provider? I ask because I see the potential for issues with the current arrangement when we are logging all historical data to 1 schema.
I don’t see what having separate schemas for department historical data buys you. What potential issues do you see with a single schema for tag history?
Based on what you have shared, I think separating batch data into another schema makes sense because this schema couldn’t be automatically restored by Ignition.
I would probably make 2 schemas for the factory: 1 for tag history, and another for batch data.
There are lots of discussions on the sql server forums about schemas and their uses, and most of the pros I have found reliable tend to not use schemas much, if at all. We don’t use them, and have many very large applications running. They primarily are useful for segregating users by permissions – is this a problem for you and this app? I notice in your snip above that you have identically named tables, in different schemas. That looks to me like a headache waiting to happen.
Thanks to you both for the input. It sounds likle the recommendation is to keep the SQL setup as it is. I guess I was concerned that with significantly increasing the number of historical tags, the table size could become an issue. From the above it seems like this is not a concern. David, I can’t see 2 tables with the same name. The intent was to avoid this.
Your SQL database structure doesn’t look so bad yet. My databases are not clear where it is not clear why. Do you happen to know how to sort all databases automatically?
I’m afraid I don’t understand your question. Please can you rephrase it?
In my setup, the schema are created manually using Microsoft SQL management studio. For the reporting data, I also manually create the tables in management studio. For the historian schema, ignition will automatically create the tables (using the default settings) once a tag is configured as a historical tag.
