Solved Data Change Table - Best Practices? (1 Viewer)

[wmix]

I am doing some database updates and would appreciate guidance from others.

We have a table that records basic details (id, name, address, city, etc..) about a location (tblProperties). Within this table we have a field called PropertyName - this field can represent an apartment complex name or a business name.

It's not unusual for an apartment complex to change names, happens more than I thought it ever would, and I need to come up with a better system to make the "old name" searchable. Example of my issue:

PropertyName: Riverview Commons

We get a phone call that the property has been sold, and they have a new name.

New name: Arise River Bend

When the business owner asks me about a property, he often refers to it by the old name, simply because he's been using that name for 15+ years. To help me with my search, I store name changes the following way:

PropertyName: Arise River Bend (Riverview Commons)

I know this is not a good way to do things, but I've never had time to make any changes and want to do those now.

I plan to create a new table to record property name changes and thinking I may only need basic information like: PropertyID, PropertyName, NameChangeDate.

I'm wondering if anyone does anything similar where you record database changes and best practices, advice you can share? Thank you.

 

[CJ_London]

Create an ‘alias’ table as a child to your main table. I have a similar situation where a club member has different member numbers depending on the activities they partake in. This comes from an online booking system over which I have no control

 

[wmix]

Create an ‘alias’ table as a child to your main table. I have a similar situation where a club member has different member numbers depending on the activities they partake in. This comes from an online booking system over which I have no control

That's a great idea, I didn't think of that. Thank you.

 

[isladogs]

Why not use a field PreviousName together with NameChangeDate in your main table? Both fields null unless a change has occurred

 

[Gasman]

And then the name changes yet again?

 

[wmix]

And then the name changes yet again?

It's amazing, but yes. I've been here less than 10 years and one property has changed names 3 times.

 

[Gasman]

I would go with a table then with perhaps the change date. However if new owners are taking it over, that could be linked to them and the property?, so could be in the junction table?

 

[isladogs]

If you need to store all previous names and date changes then a child table with a 1 to many relationship is required.
However, it sounded like you were only saving the last name change.

 

[wmix]

If you need to store all previous names and date changes then a child table with a 1 to many relationship is required.
However, it sounded like you were only saving the last name change.

Yes, I should save them all. You're right, one-to-many is a good way to go.
So many changes from my "simple" database that we only wanted to track a few things.

 

[wmix]

I would go with a table then with perhaps the change date. However if new owners are taking it over, that could be linked to them and the property?, so could be in the junction table?

I track the management company right now - but an ownership table might be a good idea, haven't thought of that one. I'll talk to the boss.

 

[GPGeorge]

It's amazing, but yes. I've been here less than 10 years and one property has changed names 3 times.

If it can change once, it can change 20 times. An alias table is the safest solution.

 

[The_Doc_Man]

Just as a thought about how to do this:

The actual property table needs an internal, unique, and permanent property number to become the prime key of the property table. This number never changes. You might have to consider rules for what you would do if somehow the property subdivides in a way to require separate handling.
The property name table lists these fields: Property Name, PropID, StartDate, EndDate. Two special cases: For dates of things where you don't know when it got its name but it was before you got control of it, make the StartDate equal to 1 Jan 100, which is the earliest date that Access can show. Any comparison will find any contemporary date to be after this earliest possible start date. Then for the dates on currently applicable names, you make the EndDate equal to 31 Dec 9999, which is the latest date that Access can show.

If there is another item that is associated with the renaming, it would be stored with the name table. Like if property company A had a name and the property had a distinct name, and then company A changed the property name... then a couple of years later, company A sold the property to company B which gave the property a third distinct name, a link to the company (A or B) would go along with the name. You would probably have a separate "parent company" name table in that case.

 

[wmix]

Just as a thought about how to do this:

The actual property table needs an internal, unique, and permanent property number to become the prime key of the property table. This number never changes. You might have to consider rules for what you would do if somehow the property subdivides in a way to require separate handling.
The property name table lists these fields: Property Name, PropID, StartDate, EndDate. Two special cases: For dates of things where you don't know when it got its name but it was before you got control of it, make the StartDate equal to 1 Jan 100, which is the earliest date that Access can show. Any comparison will find any contemporary date to be after this earliest possible start date. Then for the dates on currently applicable names, you make the EndDate equal to 31 Dec 9999, which is the latest date that Access can show.

If there is another item that is associated with the renaming, it would be stored with the name table. Like if property company A had a name and the property had a distinct name, and then company A changed the property name... then a couple of years later, company A sold the property to company B which gave the property a third distinct name, a link to the company (A or B) would go along with the name. You would probably have a separate "parent company" name table in that case.

Thanks for your reply. I do use the start date and end date for my product price table, but I never considered using it here, that is a great idea.

I'm thinking I need multiple tables. Do you see any downfalls of having things separated as follows?

tblProperty
PropertyID (PK)
PropertyAddress
PropertyCity
PropertyState
PropertyZip

tblPropertyName
PropertyNameID (PK)
PropertyID (FK)
PropertyName
NameStartDate
NameEndDate
ManagementID (FK)


We've never had a property sub-divide, but it's something I'm going to have to think about. Thank you.

 

[The_Doc_Man]

What you showed is a good start. Just remember that like ANY real-world project, initial viewpoints can be OBE - "overcome by events." So do NOT be surprised if you realized you needed something else along the way.

 

[CJ_London]

You don’t really need an end date - it will be null for the current name and one day less that the next start date for previous names and easily calculated if ever required.

End dates are really only required for data that can end - such as a contract

 

[The_Doc_Man]

It is not clear to me that whatever is being tracked couldn't end. That would be a fine point to be explored by the OP.