The more I wade in to the world of FSQL and FPMI, the more tables I seem to acquire. I fear that if I just keep throwing them all into the same database that it is going to become an unmanageable nightmare. What have people found out to be the best way to organize all of the relevant tables and data?
For instance, do I create a separate database for FMPI data, FSQL data, historical data, etc? If so, where do I put things like alarms which both systems use? I really like to keep things clean and organized, but since I am so new to the architecture it is proving difficult to accomplish.
Any guidance and/or insight would be greatly appreciated. Thanks.
You can use separate schemas to organize your data, but I think that that is better used for more of a physical separation (by project, area of plant, etc) rather than data-class separation(alarms here, history here, etc). Good naming-practices and some database planning can do wonders.
Also, you should start reading up on some database schema design theory - I see a lot of new users do things like creating tables with the same column structure but for separate machines, like “motor1”, “motor2”, etc. This is, frankly, the worst thing you can do with regards to schema design. You want to normalize all of your like data into fewer tables, and separate things with ids in columns. So you would have a “motors” table with a “motor_id” column to separate them.
A schema design program like MySQL’s Workbench can really help you visualize your database which is crucial for good planning. (dev.mysql.com/workbench/)
Visio will also work in a pinch.
This might sound like a lot - but don’t despair! Its worth it. Good database design = more easily written queries and faster queries.
I come from a computer science background so I definitely hear ya on database normalization and such. I’ve “inherited” tables similar to what you mentioned in your post and they are a definite nightmare to work with.
I think I will start organizing things by project like you said. That seems to make the most sense to me as well. A schema for each project, and another separate one for FSQL which will probably span across projects. That should make things easier if I ever have to migrate projects and such.
Maybe I’m over complicating things here, but after working on this a bit more I am still having trouble splitting things up logically…
Let’s try using an example: ACME Co. has two lines, Line A and Line B.
First you would want to make a schema for all of the shared data, right? Let’s call this one ‘acme’.
In acme you would want to put:
[ul][li]Distribution Lists[/li]
[li]Authentication Profiles[/li]
[li]Service Status[/li]
[li]SQLTags?[/li]
[li]Alarms?[/li][/ul]
Then you would create a schema for each line. Let’s say ‘lineA’ and ‘lineB’.
In each one of these you would put their respective:
[ul][li]Audit Logs[/li]
[li]Historical Data[/li]
[li]Functions / SPs[/li]
[li]I’m not sure what else[/li][/ul]
With SQLTags do you only work with that one instance you created in FSQL? You can’t have a separate instance for each project?
It also looks like there is only that one instance of alarms which you set up in FSQL. Is there a way to split that up per project, or do you just adjust the where clause to filter out the other projects?
I would argue that splitting up schemas for lines is simply an extension of the splitting up tables for motors example above, especially if the lines are similar. Also, what you’ve said is true - you only get one SQLTags, one alarm log, etc. FactorySQL doesn’t have the notion of running multiple projects. Use the where clause - let the database sort through the data for you, thats its specialty.
Let me describe what I have done and get your comments as to whether or not I went in the wrong direction. First of all I do not have a computer science background, I am an Electrical Engineer. I have studied a lot on my own but am still learning.
Our plant has 8 production lines with common support equipment. The following is how I have it laid out.
PMI_Server: Database connection
This contains data for all equipment for:
Alarms
Pens
Users
Logs
FSQL system tables
I then have a separate database connection for each line. Each line then has a separate table for each machine.
When I added the OPC items to an FSQL historical group it created a separate column for each item.
I am using one FPMI project to access all lines. When I create screens for a new line I copy an existing lines screens and modify the connection. I use the same naming convention for the tables.
My questions for all of you who have expertise in this is:
Did I fall into this category?
I do not have the same columns because the machines are different designs. I query the database schema for the column names to populate my pens table. This allows me to change/add items to the historical tables as we improve the machines without modifying the code.
Is this approach acceptable for long term growth and manageability?
Well it is hard for me to comment without a more thorough understanding of the similarities and differences between your lines. If the lines are fairly similar, then I think you’ve gone a bit off the path. I would have liked to see all lines in the same schema, ideally in the same set of tables. Then in a perfect world you could use 1 screen for all the lines (without any copying!), just by passing in the line number as a parameter to the screen.
But of course as differences arise between the lines, the promise of this ideal starts to fall apart…