Historical Data

[mekohler]

Hi, I was wondering how to deal with data that changes, but the user still needs to access data from the past. For a database that deals with development permits for properties I have a primary key that combines tax roll number and Parcel ID. However, when a parcel is subdivided, the parcel is assigned a new roll number and parcel id. It would be useful for the user to be able to access the history of the parcel before the subdivision. How would you be able to tie the old data to the new data if the primary key changes? I can set up a table that links the old primary key to the new primary key, but how would this table be updated?
Thanks,
Michael Kohler

 

[SkipVought]

Hi,

You might consider adding a column for PrevParcelID. This will give you a parent-child relationship.

So a ParcelID can be divided into two or more parcels? The original parcel row must be maintained, but must have an inactive status as of a date.

Can parcels be combined into a new ParcelID?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mekohler]

Hi thanks for the reply. Yes parcels can be combined.
Michael

 

[SkipVought]

Then you'll need a table that can show that relationship.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[johnherman]

This situation is common in Data Warehouse applications. The concept is "slowly changing dimension" and is too deep to discuss in a Tek-Tips post. You can search the internet on "slowly changing dimension" and look at the various Types and solutions. This looks like it may be a candidate for a Type 2 SCD.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)

 

[MajP]

Not sure if I fully understand what happens when a parcel is subdivided. Not sure if the original gets a new id or only the new split gets an ID. However, if the old record gets a new ID and you want to have the old children point to the old parcel with a new ID, you simply do this through referential integrity and cascade updates. If pacel abc123 is changed to abc123-2 then the DB automatically update the foreign keys to abc123-2.

 

[SkipVought]

I don't know how the mechanics of parcel subdivision works. But it seems to me that Parcel A would have a geometric description such that if you were to describe a plot in a certain specific way then it could be determined that that description could equate to the description for Parcel A and no other parcel.

So when Parcel A is subdivided, Parcel A ceases to describe a currently active parcel and the resulting current land parcels each have their own geometric description. If in the subdividing, however, Parcel A is simply redefined geometrically and some other parcel(s) are created, there would be a problem in determining parcel history it seems to me since the previous geometric description could be lost unless a parcel version history were to be maintained to describe the geometry in time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]