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.
.
|