A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Interested in thoughts on keeping the integrity of historical data



 
 
Thread Tools Display Modes
  #1  
Old April 6th, 2010, 12:37 AM posted to microsoft.public.access.tablesdbdesign
hollyylloh
external usenet poster
 
Posts: 18
Default Interested in thoughts on keeping the integrity of historical data

Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as well,
unintentionally of course, and this is usually acceptable. If for example I
need to keep the historical transaction intact as it was originally entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address. This
of course is difficult to get the user to do, they will just change the old
address to the new address.

One way to go about this is to force the user to create a new client by not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for all
the information that needs to be historically accurate. The client table thus
becomes more of an extended drop down menu for entering multiple values into
the main historical table. I really think this is the better way to go about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.
  #2  
Old April 6th, 2010, 01:05 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Interested in thoughts on keeping the integrity of historical data

We've needed to keep "historical records" (not of addresses, but let's use
that as an example)...

Given what you've described, we handled it by creating a person table, and
address table, and a person-at-address table.

That way, when a person took a new address, we didn't need to create a new
person, just the new address and a new person-at-address record.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"hollyylloh" wrote in message
...
Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in
another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as
well,
unintentionally of course, and this is usually acceptable. If for example
I
need to keep the historical transaction intact as it was originally
entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address.
This
of course is difficult to get the user to do, they will just change the
old
address to the new address.

One way to go about this is to force the user to create a new client by
not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for
all
the information that needs to be historically accurate. The client table
thus
becomes more of an extended drop down menu for entering multiple values
into
the main historical table. I really think this is the better way to go
about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.



  #3  
Old April 6th, 2010, 01:15 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Interested in thoughts on keeping the integrity of historical data

If a client has multiple addresses over time, then there is a one-to-many
relationship between client and client address. When a one-to-many
relationship exists, you need a table on the many side. So you need:
TblClient
ClientID
ClientName
other client fields that don't change

TblClientAddress
ClientAddressID
ClientID
NewAddressStartingDate
address fields

When you need to retrieve a client's address, you find the current address
by looking for the address associated with the max NewAddressStartingDate.

Steve



"hollyylloh" wrote in message
...
Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in
another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as
well,
unintentionally of course, and this is usually acceptable. If for example
I
need to keep the historical transaction intact as it was originally
entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address.
This
of course is difficult to get the user to do, they will just change the
old
address to the new address.

One way to go about this is to force the user to create a new client by
not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for
all
the information that needs to be historically accurate. The client table
thus
becomes more of an extended drop down menu for entering multiple values
into
the main historical table. I really think this is the better way to go
about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.



  #4  
Old April 6th, 2010, 02:03 AM posted to microsoft.public.access.tablesdbdesign
hollyylloh
external usenet poster
 
Posts: 18
Default Interested in thoughts on keeping the integrity of historical

Thank you, that makes sense. Yes, i was just using the address as an example.

"Jeff Boyce" wrote:

We've needed to keep "historical records" (not of addresses, but let's use
that as an example)...

Given what you've described, we handled it by creating a person table, and
address table, and a person-at-address table.

That way, when a person took a new address, we didn't need to create a new
person, just the new address and a new person-at-address record.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"hollyylloh" wrote in message
...
Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in
another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as
well,
unintentionally of course, and this is usually acceptable. If for example
I
need to keep the historical transaction intact as it was originally
entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address.
This
of course is difficult to get the user to do, they will just change the
old
address to the new address.

One way to go about this is to force the user to create a new client by
not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for
all
the information that needs to be historically accurate. The client table
thus
becomes more of an extended drop down menu for entering multiple values
into
the main historical table. I really think this is the better way to go
about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.



.

  #5  
Old April 6th, 2010, 02:11 AM posted to microsoft.public.access.tablesdbdesign
hollyylloh
external usenet poster
 
Posts: 18
Default Interested in thoughts on keeping the integrity of historical

Thank you, yes that makes sense. The address was just an example, but I think
with some close planning that is what I need to do. To continue the example,
there would be many possible addresses for the client and they would change
back and forth between each as needed, and the user would not be able to
delete an address.

"Steve" wrote:

If a client has multiple addresses over time, then there is a one-to-many
relationship between client and client address. When a one-to-many
relationship exists, you need a table on the many side. So you need:
TblClient
ClientID
ClientName
other client fields that don't change

TblClientAddress
ClientAddressID
ClientID
NewAddressStartingDate
address fields

When you need to retrieve a client's address, you find the current address
by looking for the address associated with the max NewAddressStartingDate.

Steve



"hollyylloh" wrote in message
...
Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in
another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as
well,
unintentionally of course, and this is usually acceptable. If for example
I
need to keep the historical transaction intact as it was originally
entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address.
This
of course is difficult to get the user to do, they will just change the
old
address to the new address.

One way to go about this is to force the user to create a new client by
not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for
all
the information that needs to be historically accurate. The client table
thus
becomes more of an extended drop down menu for entering multiple values
into
the main historical table. I really think this is the better way to go
about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.



.

  #6  
Old April 6th, 2010, 02:36 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Interested in thoughts on keeping the integrity of historical

In the example that you gave, you used a transaction as an example of a case
where you needed the historical data (e.g. address) This is an example of a
common areas where this is needed. If this is the main need, you might want
to start databasing the entire transaction events (e.g. invoices, e.g.
including the at-the-time billing address) as entities. (vs. treating only
certain items in them as entities, and "deriving" the invoice each time that
it is printed.)

  #7  
Old April 6th, 2010, 05:37 PM posted to microsoft.public.access.tablesdbdesign
Stephen Raftery[_3_]
external usenet poster
 
Posts: 9
Default Interested in thoughts on keeping the integrity of historical data

A way of maintaining an archive of old data might be to create a table that
has the same structure as your data table, with extra fields for timestamp
and userID.
Whenever the data in the table changes, you trigger a BeforeUpdate event
which saves the old data to the archive table, and then makes the change.
The user does not see this happen, but you have a complete archive of all
orevious changes to the table.

Stephen



"hollyylloh" wrote:

Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as well,
unintentionally of course, and this is usually acceptable. If for example I
need to keep the historical transaction intact as it was originally entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address. This
of course is difficult to get the user to do, they will just change the old
address to the new address.

One way to go about this is to force the user to create a new client by not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for all
the information that needs to be historically accurate. The client table thus
becomes more of an extended drop down menu for entering multiple values into
the main historical table. I really think this is the better way to go about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.

  #8  
Old April 7th, 2010, 08:43 PM posted to microsoft.public.access.tablesdbdesign
hollyylloh
external usenet poster
 
Posts: 18
Default Interested in thoughts on keeping the integrity of historical

Fred, thank you for your thoughts. Just to be clear, I think you are agreeing
with my first inclination as stated above? In saying "databasing the entire
transaction event" do you mean: Store all the data, that has historical
significance, in a single table? Thanks again.

"Fred" wrote:

In the example that you gave, you used a transaction as an example of a case
where you needed the historical data (e.g. address) This is an example of a
common areas where this is needed. If this is the main need, you might want
to start databasing the entire transaction events (e.g. invoices, e.g.
including the at-the-time billing address) as entities. (vs. treating only
certain items in them as entities, and "deriving" the invoice each time that
it is printed.)

  #9  
Old April 7th, 2010, 08:46 PM posted to microsoft.public.access.tablesdbdesign
hollyylloh
external usenet poster
 
Posts: 18
Default Interested in thoughts on keeping the integrity of historical

Stephen,

Thank you, that is an interesting idea. I will keep that as a possibility as
I make the decision on this.

"Stephen Raftery" wrote:

A way of maintaining an archive of old data might be to create a table that
has the same structure as your data table, with extra fields for timestamp
and userID.
Whenever the data in the table changes, you trigger a BeforeUpdate event
which saves the old data to the archive table, and then makes the change.
The user does not see this happen, but you have a complete archive of all
orevious changes to the table.

Stephen



"hollyylloh" wrote:

Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as well,
unintentionally of course, and this is usually acceptable. If for example I
need to keep the historical transaction intact as it was originally entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address. This
of course is difficult to get the user to do, they will just change the old
address to the new address.

One way to go about this is to force the user to create a new client by not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for all
the information that needs to be historically accurate. The client table thus
becomes more of an extended drop down menu for entering multiple values into
the main historical table. I really think this is the better way to go about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.

  #10  
Old April 7th, 2010, 09:47 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Interested in thoughts on keeping the integrity of historical

Storing all the data, that has historical significance, in a single table is
a very bad idea! In a single table design, you would need to type in the
address for each transaction. What happens if a user mistypes a part of an
address that was previously used by your client. Now you have two addesses
for the client when actually it should be one - the client used the same
address both times. The correct design is a TblClient and a
TblClientAddress. You just need to select the correct client address at each
transaction. If the client has a new address at the time of the transaction,
you need to first add the new address to TblClientAddress and then select
the new address as you enter the transaction.

Steve



"hollyylloh" wrote in message
...
Fred, thank you for your thoughts. Just to be clear, I think you are
agreeing
with my first inclination as stated above? In saying "databasing the
entire
transaction event" do you mean: Store all the data, that has historical
significance, in a single table? Thanks again.

"Fred" wrote:

In the example that you gave, you used a transaction as an example of a
case
where you needed the historical data (e.g. address) This is an example of
a
common areas where this is needed. If this is the main need, you might
want
to start databasing the entire transaction events (e.g. invoices, e.g.
including the at-the-time billing address) as entities. (vs. treating
only
certain items in them as entities, and "deriving" the invoice each time
that
it is printed.)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.