Results 1 to 5 of 5

Thread: Database design for storing and quering historical data

  1. #1

    Thread Starter
    Hyperactive Member Krokonoster's Avatar
    Join Date
    Jan 2010
    Location
    Cape Town
    Posts
    448

    Database design for storing and quering historical data

    Hi,
    More of a loose (very) idea and hobby project.

    Want to design a database storing members, their vehicles, etc. which are pretty simple.

    But (never did this before) want some kind of auditing in place so that I can do a query for say someone by name, surname and address but it also search in past addresses.

    One approach I thought about is having say a "Member" table with current data and each time something change or are deleted, I write the data before the change to a "Member_History" table.

    So let's say I'm looking for "Peter Pan" in "New York" and the Peter Pan I'm looking for have a previous address in New York, but currently live in Washington, I will get his as part of possible matches.

    Hope this make sense? Perhaps there is some standard approach to this, but like I said, never did anything like this (other than simple auditing ... when and who changed what)

    I know it's frown upon, but while at it: I'm so used to MS Sql and like a bit of a challenge. Say I want to build a database very stable, scale-able and secure, which of the other players are good? Oracle (Enterprise...no $$), MySql, etc?

    Cheers,
    Krok


  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Database design for storing and quering historical data

    Not sure if there is a equivalent for MSSQL but for MySQL I find MySQL Workbench, can help with designing the database.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Database design for storing and quering historical data

    Ah - temporal databases - the holy grail.

    http://en.wikipedia.org/wiki/Temporal_database

    The ansi/iso standards people keep failing to address this issue - leaving us to hand-craft our own solutions individually.

    I've done addresses like the attached image. Standard ADDR_T table and then an Archive table.

    In order for that to serve your purpose you would have to craft a VIEW that did a UNION on both tables - so that you could easily setup a WHERE clause to search the "combined set of address data" for a NEW YORK address (regardless of timeframe).

    If you wanted to design the address table to more closely model the reality of address changes then you would take my first "ADDR_T" table layout and add a field just after MasId for "START DATE". Now you know the START DATE of every address recorded for any given Master Id. Primary key becomes MasId+StartDate (compound PK).

    Downside to this is knowing which row is the "active" row for a person. You could use a VIEW that gives you MAX(StartDate) for any given Master Id - and then use that in a JOIN. This is the safest method.

    Alternative would be to put a STATUS field in the row. Although being responsible for making that STATUS field accurate is really a daunting task.

    side-thought: this might actually be a good use of a TRIGGER - setting the status of prior addresses to inactive when a new address is added - I wonder...
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    Hyperactive Member Krokonoster's Avatar
    Join Date
    Jan 2010
    Location
    Cape Town
    Posts
    448

    Re: Database design for storing and quering historical data

    Interesting read this temporal database..thanks NightWalker. (give one some more ideas)

    Makes perfect sense Szlamany...actually walked around with an idea roughly in that line.

    Thanks for the replies guys, especially the last. You took some time to write all that. Much appreciated, really!


  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Database design for storing and quering historical data

    I use pretty much the same aproach as Szlamany except that I tend not to separate it out to a history table. I keep it in a single table with start and end dates. The record with a null end date is the current one. The upside is that it's easier to query and, because it's in a single table, it's easier to implement apropriate integrity constraints. The downside is that it's much more work for the DBMS to find the current address for someone (which is the address you'll probably want most of the time) because it's got to query across a much larger table. Indexes help with that though.

    I've also see a system where they tracked the "transactions" instead to record a history. In the case of addresses that means focus on recording each time someone moves rather than a list of addresses they've lived at. It actually produces a very similar design but the shift in emphasis makes it slightly better for presenting a history.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Click Here to Expand Forum to Full Width