SQL database structure recommendations

Ignition 8, MSSQL

Hi All

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.

Any advice would be great. Thanks.

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.

2 Likes

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.

1 Like

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.

On closer look, I see you’re right. All are under the dbo schema, and there are prefixes to distinguish the manufacturing cell.

1 Like