Results 1 to 7 of 7

Thread: Timeline design

  1. #1
    Join Date
    Feb 2014
    Posts
    4

    Unanswered: Timeline design

    Hello!
    I need to create a timeline of events and read data from it. The events signify that the state of something has changed, meaning effects span through time until another event.
    We can simplify like this banking example:
    -open customer account
    -change limit on account
    -close account
    Now, normally I would create a table like this
    Code:
    create table time
    ( key integer not null
    , valid smallint not null
    , data text not null
    , date_from date not null
    , date_to date not null
    , primary key (key, valid, date_from)
    );
    I would insert account record and set date_from to current and date_to to max date (or something else if end date was known)
    But I need history data. So, when something changes (data column), I need to end previous record and add a new one with the old end date.

    Is there some other way? How do you deal with timelines?

    I thought i could only write events:
    Code:
    create table time
    ( key integer not null
    , valid smallint not null
    , data text not null
    , date date not null
    , primary key (key, valid, date)
    );
    
    --opened, changed 3 times, still valid
    insert into time values (1, 1, 'Mercury', '2013-01-01');
    insert into time values (1, 1, 'Venus', '2013-01-10');
    insert into time values (1, 1, 'Earth', '2013-01-25');
    insert into time values (1, 1, 'Mars', '2013-04-01');
    --opened, changed once, still valid
    insert into time values (2, 1, 'Bonnie', '2013-01-01');
    insert into time values (2, 1, 'Clyde', '2013-01-30');
    --opened, not changed, still valid
    insert into time values (3, 1, 'Donnie', '2014-01-12');
    --opened, changed, closed
    insert into time values (4, 1, 'Donnie', '2014-01-10');
    insert into time values (4, 1, 'Donnie', '2014-01-30');
    insert into time values (4, 0, '', '2014-02-10');
    --invalid
    insert into time values (5, 0, '', '2014-02-12');
    --opened, closed on same day in the future
    insert into time values (6, 1, 'Dawkins', '2014-12-12');
    insert into time values (6, 0, '', '2014-12-12');
    --open, closed
    insert into time values (7, 1, 'Richard', '2014-02-12');
    insert into time values (7, 0, '', '2014-02-28');
    --opened, changed, closed, reopened, closed 
    insert into time values (8, 1, 'Jupiter', '2013-03-12');
    insert into time values (8, 1, 'Saturn', '2013-04-12');
    insert into time values (8, 0, 'Uranus', '2013-05-01');
    insert into time values (8, 1, 'Neptune', '2013-06-20');
    insert into time values (8, 0, '', '2013-08-31');
    With this selects get more complicated as I don't have the end date in the same row, but the inserts are less complicated as i don't have to worry obout fixing dates on older records.

    I haven't thought this through. I would just like some advice.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da! I originally replied thinking that I'd read your post in the SQL Server forum. It wasn't until I'd posted my reply that I realized you were using DB2. My bad, but the comments still apply so I've just revised this post.

    There are two different ways to handle the sequence problem that you've described. If you are using DB2 versions which support the OVER clause, it elegantly solves sequence problems. If you are running a DB2 version that does not provide OVER, then you can use nested sub-queries to get the same effect in any version.

    -PatP
    Last edited by Pat Phelan; 02-11-14 at 09:45.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2014
    Posts
    4
    Ok, you suggested OLAP functions, and that is an option db2 version - wise.
    But which approach should I use? Is there some other option i don't know about? Is there some book or paper i could refer to? What do most people do when faced with such things?

    The reason I am thinking of alternative approaches is I will be getting the data from outside my application and the simplest and fastest way is to just do inserts of new states without caring what is already in that table. And along with that I automatically get history data.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You might be able to make use of the DB2 application-period temporal tables:

    Application-period temporal tables
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2014
    Posts
    4
    Ok, you suggested OLAP functions, and that is an option db2 version - wise.
    But which approach should I use? Is there some other option i don't know about? Is there some book or paper i could refer to? What do most people do when faced with such things?

    The reason I am thinking of alternative approaches is I will be getting the data from outside my application and the simplest and fastest way is to just do inserts of new states without caring what is already in that table. And along with that I automatically get history data.

  6. #6
    Join Date
    Feb 2014
    Posts
    4
    Quote Originally Posted by n_i View Post
    You might be able to make use of the DB2 application-period temporal tables:

    Application-period temporal tables
    I'll have a closer look into that. Looks interesting.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by Mandor View Post
    Hello!
    I need to create a timeline of events and read data from it. The events signify that the state of something has changed, meaning effects span through time until another event.
    We can simplify like this banking example:
    -open customer account
    -change limit on account
    -close account
    Now, normally I would create a table like this
    Code:
    create table time
    ( key integer not null
    , valid smallint not null
    , data text not null
    , date_from date not null
    , date_to date not null
    , primary key (key, valid, date_from)
    );
    I would insert account record and set date_from to current and date_to to max date (or something else if end date was known)
    But I need history data. So, when something changes (data column), I need to end previous record and add a new one with the old end date.

    Is there some other way? How do you deal with timelines?

    I thought i could only write events:
    Code:
    create table time
    ( key integer not null
    , valid smallint not null
    , data text not null
    , date date not null
    , primary key (key, valid, date)
    );
    
    --opened, changed 3 times, still valid
    insert into time values (1, 1, 'Mercury', '2013-01-01');
    insert into time values (1, 1, 'Venus', '2013-01-10');
    insert into time values (1, 1, 'Earth', '2013-01-25');
    insert into time values (1, 1, 'Mars', '2013-04-01');
    --opened, changed once, still valid
    insert into time values (2, 1, 'Bonnie', '2013-01-01');
    insert into time values (2, 1, 'Clyde', '2013-01-30');
    --opened, not changed, still valid
    insert into time values (3, 1, 'Donnie', '2014-01-12');
    --opened, changed, closed
    insert into time values (4, 1, 'Donnie', '2014-01-10');
    insert into time values (4, 1, 'Donnie', '2014-01-30');
    insert into time values (4, 0, '', '2014-02-10');
    --invalid
    insert into time values (5, 0, '', '2014-02-12');
    --opened, closed on same day in the future
    insert into time values (6, 1, 'Dawkins', '2014-12-12');
    insert into time values (6, 0, '', '2014-12-12');
    --open, closed
    insert into time values (7, 1, 'Richard', '2014-02-12');
    insert into time values (7, 0, '', '2014-02-28');
    --opened, changed, closed, reopened, closed 
    insert into time values (8, 1, 'Jupiter', '2013-03-12');
    insert into time values (8, 1, 'Saturn', '2013-04-12');
    insert into time values (8, 0, 'Uranus', '2013-05-01');
    insert into time values (8, 1, 'Neptune', '2013-06-20');
    insert into time values (8, 0, '', '2013-08-31');
    With this selects get more complicated as I don't have the end date in the same row, but the inserts are less complicated as i don't have to worry obout fixing dates on older records.

    I haven't thought this through. I would just like some advice.
    If you don't want to insert value into "date_to" column you have to make this column NULLable, or NOT NULL WITH DEFAULT

    Lenny

Tags for this Thread

Posting Permissions

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