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.




Relevant Pages

  • Re: Best way to do so.
    ... It's a design mistake to try and optimize storage. ... you maximum flexibility and ease in conducting ad-hoc querying etc. ... > customer itself? ... > but as what I see, I've about 50'000 search/month, and I don't store them ...
  • Re: report on customer rage
    ... >> verbatim FYI for those with customer service nightmares (and I ... >> As shoppers head into the holiday shopping season, ... >> A shopper at a Michael's craft store is behind a man doing a price ... who rebuilt yur kombi for ya? ...
  • Re: TSWLTH revisited
    ... Not the company; not the store manager. ... to JoAnn Fab. ... That, of course, isn't true, and has been replaced by "The customer ... nobody's mind is going to be changed about JoAnn/s Fabrics by ...
  • Re: Blockbuster (Part 3)
    ... Blockbuster employees go through. ... other customers in the store, because he started off, so ... and a customer in line laughed. ... "If you insist that I pay the late fee, not only am I walking out ...
  • Re: Blockbuster (Part 3)
    ... other customers in the store, because he started off, so ... and a customer in line laughed. ... Manager's tie, and YANKED his head down until it crashed ... fee that I felt was unfair. ...