Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2016
    Posts
    3

    DB Design for SCADA

    Hi All,
    I am working on designing a DB for a SCADA system and trying to understand the best approach to tables for the analogue sensors.
    Potentially I could have 1000 (maybe even more) sensors across this network all needing to write back to the DB.

    I plan to have a table detailing grouping of inputs into logical nodes and another grouping logical nodes into groups.
    At this point I am unsure how to store the actual samples, however I am thinking of a new table for each input. This will allow time stamped data to be stored separately for each input.
    This would mean that ever time a new input is added to the network, a new table would need to be created.
    It would also allow for easy archiving of data when inputs are removed and prevent any corruption of a single sensors table affecting all of the data.

    The problem with this is I am not sure how to then set up the relationships with the other tables unless I enter the input ID into every entry which would be redundant data.
    Any help would be greatly appreciated

    Thanks

  2. #2
    Join Date
    May 2016
    Posts
    89
    Hi dmacleod,

    To start, try to list objects with their properties and information that you want to store.

    Spontaneously, I identify sensor object. But how do you differentiate each sensor? What kind of information do you need to store about them? Do you store the historical data too?
    Where are located each sensor?

  3. #3
    Join Date
    Oct 2016
    Posts
    3
    With the info on the sensor, ie units of measure, calibration figure, description, etc I was going to have a single table for all sensors and create a sensor ID for the primary Key.
    The historical data is where I am struggling as there could be 86400 samples per day and I want to be able to recall and display historical data up to 12 months old.
    At the moment I have a table (name of the table is the sensor ID)for the historical data that has an AI PK, sensor data, date time stamp.
    I am not sure if this is the best way to do it or even how to best link this back to the table that has the information.

  4. #4
    Join Date
    May 2016
    Posts
    89
    Hi,

    With your description , we can model like this:

    The conceptual data model
    Click image for larger version. 

Name:	sensor_cdm.jpg 
Views:	5 
Size:	29.1 KB 
ID:	17171

    The physical data model
    Click image for larger version. 

Name:	sensor_pdm.jpg 
Views:	7 
Size:	38.5 KB 
ID:	17172

    But an idSensor is just a surrogate key and is not enough to differentiate each sensor? So could you explain me how do you collect measures, I mean technically?
    Last edited by informer; 10-21-16 at 18:02.

  5. #5
    Join Date
    Oct 2016
    Posts
    3
    Sorry I really don't understand the 2 data models.

    Technically, I currently have a RPI (also looking at other hardware platforms) running a software SCADA package.
    The RPI has several analogue sensors connected via A/D converter. SCADA package has archive commands that connect to MYSQL DB and store data measurements and date-time stamps. By going down to sensor level and giving them their own unique ID I will have the flexibility to create virtual nodes, or group of sensors that are not physically connected to the one RPI.
    I hope this helps explain how I have my setup.

    thanks
    Darren

  6. #6
    Join Date
    Jun 2017
    Location
    Ukraine
    Posts
    11

    DB Design for SCADA

    For now i dont see the link to a real Midibox project. Even seems a bit commercial to me. So moving to Design Concepts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •