Database design question

[C Tate]

This question refers to an article I read on database normalisation tips in
the msdn.microsoft.com/library.

The author says:

"Sometimes data needs to be de-normalized to preserve information that may
change over time.

In our simple example of an invoice linked to the customer table via a
custumor ID number, we may need to preserve the customer address at the time
the invoice is issued (not at the time it's created, because the customer
information may change between the two events). If we did not preserve the
address at the time the invoice was issued, and we had to update the
customer information in future, we may not be able to confirm the exact
address to which a particular invoice was sent. This could be a huge
business problem."

My question is this. What is the best way to ensure that a database
preserves the information at, say, the time the invoice is issued supposing
the customer address changes after an order is placed?

 

[Albert D. Kallal]

In you example, you have no choice, but to copy the customer address
information to the invoice. I have a few applications where in fact if the
customer address is changed, then some information in the database is a
sense is now incorrect. However, in our case, even when referring to some
"old" document, my client preferred having the new address information as
opposed to some old address.

However, yes..if you need to save that information, the you need to save
it!. This is no magic at all, and it simply means that you will actually
have to copy the data. And, yes, if the address is updated in the next day
or so, then your current invoice will have the INCORRECT address. You have
to weigh the pros, and cons, as each situation will be different depending
on what you, or your client wants (or needs) to do.

If a wrong address is discovered, and phoned in, then how will you update
those existing orders that have the wrong address?

As mentioned, each approach has pros, and cons...and it will depend on what
kind of business you are running, and what needs to be accomplished. There
is no right or wrong answer here.

 

[Dave Jones]

Colin,

Normally you would have a customer table, one (or more
fields) holding the customer's current address updated
when the address changes. With the scenario you mention,
what you need to do is to hold the current customer
address in one or more fields in the invoice table when
the record for the invoice is created. This means that if
the address in the customer record in the customer table
is updated, the invoice record will show the address to
which the invoice was sent.

Dave

 

[C Tate]

Does that mean, then, that the address will have to be typed in twice? Once
in the customer table and once in the invoice table?

 

[Ray]

You could hold a date range against the Customer address,
which would store the start date and end date for when
that address was valid. In that case if they were at that
address from say 1st Jan 1990 to 31st Dec 2000 then all
invoices sent out in that period would have been sent to
that address. For the new address you would hold a start
date of 1st Jan 2001 and any invoices sent out after that
date would have been sent to the new address.

 

[Albert D. Kallal]

No, you always have some code copy the customer when you whack the add new
invoice button.

However, as mentioned, a very nice solution is to have an extra table
called:

tblcustomer
----->tblAddress

For each new address you add to the customer, you simply add a new
tblAddress record. In that tblAddress list, you can also have a check box
for "active" address. This is a ideal solution, as then changing a incorrect
active address will thus mean that any active and new outstanding invoice
will now reflect the correct address without having to update anything.

My original design suggestion is most certainly a compromise, since updating
the address would mean that old invoices would show a different address they
originally had. The above design solves that problem.

Regardless, in any design that you use, you can certainly use code to copy
the address each time over and over. However, the whole idea of a relational
database is to come up with a design that avoids the need to copy data. With
a good relational design, we don't have repeating data, and further this
means you don't even write code to "copy" the data either.

 

[C Tate]

This sounds like a great idea. However, as I am really just a beginner I
would appreciate it if you could clarify a little. How do I design this new
table? It sounds like two tables! What is meant by 'for each new address you
add to the customer, you simply add a new tblAddress record'? Sorry to
appear so stupid!

 

[Albert D. Kallal]

This sounds like a great idea. However, as I am really just a beginner I
would appreciate it if you could clarify a little. How do I design this new
table? It sounds like two tables! What is meant by 'for each new address you
add to the customer, you simply add a new tblAddress record'? Sorry to
appear so stupid!

You are correct, I am suggesting to use two tables:

Of course, remember, while you don't have to write code to "copy" the data
over and over with the above design, you certainly do increase complexity of
the application. Just printing a simply mailing label now means you have to
do a relational join between two tables. When you become very experienced,
then this additional work is not much, and does not slow down the
development rate. However, when just staring out, you may have to comprise
your designs.

Anyway, the above data entry screen would thus have things like customer
name etc. However,the active mailing address would be in a sub-form, and
would be based on the address table. You could/would also add a field to
tblCustomer to "set" what the active address currently is. (a simple combo
box that lets you select which address record is active).

To get a idea of how sub-forms work, take a look at the northwind sample
database.

For some reading on normalization of data, check out:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613