INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Historical Data

Historical Data

(OP)
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

RE: Historical Data

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Historical Data

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

RE: Historical Data

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Historical Data

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)


RE: Historical Data

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.

RE: Historical Data

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close