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?
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
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...
*** 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".
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