Handling name changes

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.

That could be a quite complex scenario.
You would need to store the id of the customer with the record and have a
separate table for the name. Presumably, you would also need a date when the
name became effective and you would need to store multiple other names with
associated effective dates. This would make running a query a nightmare. I
would recommend referring to people by their most current name and use the
historical names only where absoloutely necessary.

-Dorian

"Tim Bales" wrote:

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.

I think that your boss is correct. This is an example of storing what
happened at a point in time. One example is Orders. You want to know to whom
and where you shipped the order plus how much it cost on that particular day.
If they have changed their name, address, phone number, etc., you still want
to know what it was back then.

While it's possible to set up another table or three and track the person's
name at a particular time, most people would denormalize their table design
and store the information in each order for such cases.

Another method is to create another table and store all the information
about each order in the table.
--
Jerry Whittle, MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tim Bales" wrote:

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.

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

"Tim Bales" 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.



Thanks,



Tim.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Bales" 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.

On Jan 3, 1:26 am, "Allen Browne" wrote:
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.

--