Handling name changes

[Tim Bales]

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.



Thanks,



Tim.

 

[Jeff Boyce]

Tim

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

Tim, you've had valid 2 approaches suggested. It might help to give some
examples of when each would be useful.

If you were setting up a police database or similar register, it would make
sense to create a related table with a one-to-many relation so you can store
the historical data with the past names. The way you described it, the
fields would be:
ID AutoNum
ClientID relates to the client in the main table.
EffectiveDate Date/Time (when this name became effective)
Surname Text
FirstName Text
...

But as Jerry and Jeff pointed out, you then need a subquery to to find out
each person's current name, which will make the database inefficient. It
also doesn't cope with the fact that some people use multiple names at the
same time - not just criminals, but say a woman who is recently re-married
and in some contexts uses the same name as her children but in other
contexts prefers the same name as her husband. So, you might store the
person's main current name in the Client table (making it easy to query),
and have a related table of aliases (rather than past names.) You can now
UNION the clients and aliases tables when you need to run a search on either
name.

In many cases, though, that's overkill. All you really need to know is that
when you set that letter on 1/1/2004, it was addressed to Mr and Mrs Jones.
So, storing the addressee (along with the clientid) in the Letters table is
all you need. In other words, you have a history of what name *you* used in
corresponding with clients at any time, rather than trying to maintain a
history of all names your clients have ever used. This is what Jerry and
Jeff have suggested, and it keeps things very simple if that's what you need
to know.

Hope that helps you pin down what you need to do.

 

[Jamie Collins]

ID AutoNum
ClientID relates to the client in the main table.
EffectiveDate Date/Time (when this name became effective)
Surname Text
FirstName Text

But as Jerry and Jeff pointed out, you then need a subquery to to find out
each person's current name, which will make the database inefficient.

An alternative view is that the start and end dates for each customer
detail history are two element of a single atomic fact, therefore both
dates should be in the same row i.e.

replace

EffectiveDate Date/Time (when this name became effective)

with

start_date Date/Time (when this name became effective)
end_date Date/Time (when this name became ineffective)

Not only does this deisgn provide more information (e.g. delete a row
from the history and you will see a missing period; using a single
effective date a deleted row will implicitly 'change' the facts) but it
makes the SQL DML (i.e. queries) easier to write: the subquery becomes
a simple JOIN e.g.

SELECT Orders.OrderID, CustomerDetailsHistory.CompanyName
FROM Orders INNER JOIN CustomerDetailsHistory
ON CustomerDetailsHistory.CustomerID = O1.CustomerID
WHERE Orders.OrderDate BETWEEN CustomerDetailsHistory.start_date AND
CustomerDetailsHistory.end_date;

Jamie.

--