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 3rd 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
