none
Advise on history data structure

    Question

  • Dear all,

    IN our business we have a table of parameters which are machine data.
    We need tto track any change which occurs in a parameter, for monitoring purpose and history data report.

    We can see this as same exemple of an Electric car for instance which have a set of technical parmeters which is store in the cars and we would like to keep track of those parameters as an historw analysis.

    The key question I hqve is what and how can be the historw storahe for my machines :

    1 - Do I need to better have totally separate table dedicated to history data and different from Technical parameters data?

    2 - Should I handle history data in same table as current parameters ?

    3 - Should I create a totaly separate History database with only history tables ?

    Thnaks for advise on best and more flexible approach, my personnal thinking goes more on option 2 maybe but have no idea how to structure it

    thanks for help

    regards

    Friday, May 25, 2018 7:59 PM

All replies

  • 1. Yes

    since the relationship is one to many for every parameter i.e multiple  entries for each parameter for every change

    You need to have a separate table for tracking the history of change

    the table will have reference id of the parameter, machine id,actual value and a datetime based column to indicate capture time for the value at a bare minimum

    2. You may handle it in the same table or use a separate table

    The decision depends on how frequently you analyse on the history

    If your common analysis is on change of values for a parameter over a period of time like a trend or some kind of comparison between different parts of historical periods, then keeping them in same table would be fine

    But if you're only interested in latest data for most of the time and frequency of querying historical data is less, then you shall keep them in separate table. Also keep in mind historical table will grow to huge size quite quickly due to the volume of data involved so you may also need to formulate a archiving strategy based on your business case to move old data to archive based on the retention period required.

    3. Thats also a strategy that you can adopt.

    Features like CDC already do this. CDC or Change Data Capture creates a separate schema with tables that capture history of data changes happening on the main tables. It captures every DML changes automatically in the change tables which you can use for tracking over period of time. It makes use of transactional logs under the hood for this purpose

    https://www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, May 25, 2018 9:48 PM
  • Thanks for your reply.

    Just as an exemple what could be the table structure from oprtion 1 and 2 when linked to machine and parameter tables?

    have trouble to identify the best approach for good flexibility and performance

    thanks for help and sample

    regards

    Saturday, May 26, 2018 7:55 AM
  • Thanks for your reply.

    Just as an exemple what could be the table structure from oprtion 1 and 2 when linked to machine and parameter tables?

    have trouble to identify the best approach for good flexibility and performance

    thanks for help and sample

    regards

    option 1 would look like this

    ParameterValues (Maintable)
    ------------------------------------
    UniqueKey - Primary key
    ParameterID - Reference ID to Parameter table
    MachineID - reference ID to machine table
    Value - the parameter value
    CaptureDate - audit column storing date and time of entry
    
    
    ParameterValuesHistory
    ----------------------------
    HistoryID - PK
    ParameterID
    MachineID
    Value
    ValidFrom - date column indicating the start period
    ValidTo - date column indicating end period for the value
    

    2.

    all will be in single table as below

    ParameterValues
    ----------------------------
    HistoryID - PK
    ParameterID
    MachineID
    Value
    ValidFrom - date column indicating the start period
    ValidTo - date column indicating end period for the value ( will be NULL for the latest entry
    IsCurrent - boolean column will be 1 for latest value

    Please note that I've just added only minimum columns as per the description so in actual case you need to add other related columns also to the tables.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, May 26, 2018 8:38 AM
  • I have few question bqsed on your samples:

    Option 1 :

    Capture date is used as a time stamp when value is change the time time stamp will change ? Or does that file is used as trigger to notice a change

     and then will insert a record in history table ?

    AT whic time the hiostory table will be updated with new inserted record and what is teh used of ValidFrom and Validto field ?

    Option 2 :

    For all in same table a single parmeters can be duplicated manw time in this same table as long as a new value change ?

    Can you help me to understand those 2 scenario to see when data will be updated based on current and historw data for a parmeter with sample data ?

    I need to verify the way I can use the history.

    thanks for your help

    Saturday, May 26, 2018 10:12 AM
  • any advise on my last questions ?
    Monday, May 28, 2018 6:41 AM
  • I have few question bqsed on your samples:

    Option 1 :

    Capture date is used as a time stamp when value is change the time time stamp will change ? Or does that file is used as trigger to notice a change

     and then will insert a record in history table ?

    AT whic time the hiostory table will be updated with new inserted record and what is teh used of ValidFrom and Validto field ?

    Option 2 :

    For all in same table a single parmeters can be duplicated manw time in this same table as long as a new value change ?

    Can you help me to understand those 2 scenario to see when data will be updated based on current and historw data for a parmeter with sample data ?

    I need to verify the way I can use the history.

    thanks for your help

    Capture date is used to indicate the date and time at which a value was captured for a parameter.

    For every new insert it will add the already existing value to history table as a new entry with ValidFrom as the capturedate value that was present in the table before the new insertion and ValidTo as current capture date.

    Every time a new entry is inserted for a parameter, previous entry gets into history table. ValidFrom ValidTo determines the time period during which the value was in effect for the parameter

    Option 2

    Else if all in one table, parameter entries will repeat for each new value

    I didnt understand the second part

    Give me  a sample and elaborate on what you're asking on and then i'll help.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, May 28, 2018 7:18 AM
  • thanks it is clear enough for the time being.
    great help.

    Base on your experience do you know a good book which explain those databse design to go based on different scenarios ?

    Monday, May 28, 2018 8:09 AM