Database Help

Hi,

I have a question about setting up a couple of database tables.

I'll try to explain using an example:

We have a customer (stored in a customer table)

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Who was sent an invoice for an order he placed. That invoice was sent to his current address at the time (123 Cherry Lane).

Let’s say he moved to a new address. We would update his customer record accordingly.

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 987 Apple Road

City: Dubbo

State: NSW

PostalCode: 2830

Now, let’s say that we want to be able to look back at JDOE’s invoices and see where they were shipped too. This means we have to store that address somewhere.

Now if we said just store it on the invoice table like:

InvoiceNo: 0001 (Primary Key)

CustomerCode: JDOE (Foreign Key)

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Amount: $300.00

That would break 3^rd normal form as the address doesn’t depend on the primary key (InvoiceNo [0001]).

We want to be able to see the address from the invoice table via a relationship/lookup/computational column/whatever.

How would we go about doing this in SQL Server version 8?

Cheers,

Pete

I think it is pretty common to have a set up like that. So you might have have a table(s) that lists default values for customers and an actual invoice table that includes actual ship address. You can divide it up for storage and performance reasons as necessary but most companies need to have that historical info available.

My two cents :-)

|||

Hi,

You can have an additional column say address2 for the current address. I know this sounds naive, but sometimes this is the best way to get things done. Now you may ask, what if I have more than 2 addresses, then boy ur going to have a tough time coming up with a schema, which stores historical information.

Good Luck and let me know, how u went about it.

PP

|||

G'day,

Not be blunt, but the above suggested example is very poor design. One particular (and there are several) solutions to this is to first separate the address details from the customer details:

Customer Table

CustomerCode, CustomerName,

Address Table

CustomerCode, AddressID, Addresstype, line1, Line2, PostCode,State

This will then allow a customer to first of all have more then 1 address recorded (physical, postal, work, home etc) as well as almost conforming to 3NF (the State column would in fact need to become a "StateID" column lookup into a "State' table...).

Or, if you really didn't want to store multiple addresses, just keep your same design and create a trigger on the customer table to record any address details that change into a seperate table (example only):

CREATE TRIGGER CustomerAddress_upd

on CustomerTable AFTER UPDATE

AS

BEGIN

IF ( UPDATE(Line1) OR UPDATE(PostCode) ) --list all required cols here

BEGIN

INSERT INTO AddressAudit (CustomerCode, Line1, PostCode, DateChanged)

SELECT CustomerCode, Line1, PostCode, getDate()

FROM CustomerTable c

JOIN Inserted i ON c.CustomerCode = i.CustomerCode

END

END

Then, if required, just query the audit table.

Cheers,

Rob

sql