Re: Handling name changes


I'm with Jerry. If the "name-as-of-that-date" is needed (if the boss says
it's needed, ...!) it might be easier to store that information (in addition
to keeping the "as-of-now" information in your Person table.

The downside of doing this is that you could use a query to see a person's
name, but that name might not match the person's name in the record you are
looking at (i.e., the "historical" record).

One solution to that issue is to store a way to connect together all the
names of the same person... and another is to store a start/end effective
date table of persons' names (but you'd still need a way to connect
different names for the same person). One way or the other, if you allow
different names for the same person, you need a way to connect the different
names together, so you know that Mary Jones and Mary Smith are the same


Jeff Boyce
Microsoft Office/Access MVP

"Tim Bales" <timbales@xxxxxxxxxxxxx> wrote in message
How do you accommodate for name changes when designing a database? For
example in Northwind they store the Customer ID in the Orders table not
the customer name, which is what have always heard should be done, but my
boss tells me that a customer or patient, etc. can changer her/his name
and you still want to see past transaction reports with her/his name at
the time the transactions happened, for that you store the ID but also the
name of the customer in your Orders table. Is that the correct way to
handle this kind of situations? This seems like a very common scenario so
there must be best practices about how to handle the design of the tables
and their relationship. Could somebody post a brief description of table
names, fields and relationship to cover this scenario in the design
correctly? I don't remember seeing an explanation in any database book I
have read.