This may sound like a dumb question, but “Where do you store addresses in the database of an e-commerce site?”
I feel comfortable with 3NF, but “in the real world”, things don’t seem as obvious.
Here are some address types I came up with…
Permanent Address —> where we send our thugs to collect unpaid bills
Billing Address —> associated with credit card; may or may not be Perm Addy
Shipping Address —> where the goods go; likely different from Perm and Billing Addy’s
It is my feeling that you must store the Billing and Shipping Address in the Order table to preserve history, even if it technically breaks 3NF.
I am also leaning towards storing a “Permanent Address” in the Customer table in case we do need to track people down. (This actually could be a seperate table where I not only store the Contact/Permanent Address, but also keep a history of everywhere someone claims to have lived.)
There could then be an “Address Book” table where Customers can add (and delete) Billing and Shipping Addresses that change over time and situation. (As long as we keep an address “on file” in the Customer and Order table, we have protected ourselves in my database blows up and/or we are audited.
My end goal is to protect ourselves from liability and data corruption. (If that means I don’t get an award in 3NF, so be it…)
What is the common approach in e-commerce sites with these issues?
That’s the usual example given for normalisations that you would undo when going from your logical 3NF (or 6NF) design to the practical physical implementation where redundancies are deliberately introduced for efficiency reasons. In the real world you’d never normalise that part of the address in the first place.
With that particular example there are almost no instances where you’d change one of the fields without changing the others as well since it is extremely rare for someone still living at the same street address to suddenly find themselves in a different city or state.
The billing and shipping addresses are related to a specific order and will never change once the order has been paid for and shipped regardless of any changes to the “permanent” address. Also, just because the billing and/or shipping address does change on a particular order before it has been paid/shipped does not in any way imply that any other outstanding orders with the same address need to also change.
Therefore each and every billing and shipping address needs to be stored separately from every other one even where that address is identical to other addresses you have stored because each can change independently of all the others. Each is dependent on a different primary key - that of the particular order it belongs to.
Not storing them separately would mean that your database isn’t normalised because combining them together implies a relationship between the addresses that does not exist.
You’d be amazed - or not - at how many “experts” on some Microsoft Access user groups who would disagree!
you’re well on the right track
Thanks.
as for the 3NF stuff, i suggest you look up the definition of 3NF (you can easily find half a dozen different versions, none consistent with the others, but in a nutshell, 3NF deals with the relationship of non-key attributes to the key), and i’m sure you can bend at least one of these definitions to your situation to prove that storing the customer data at time of order into the order table is not in violation
What you and felgall wrote is consistent with my logic.
note that 3NF says nothing about redundancy or repetition, which is what many people claim they are trying to avoid when they go splitting stuff off into separate tables with auto_increments
True.
I think it is taking things too literally ad not bending “theory” to the “real-world”.
By definition, having City and State and possibly Zip Code is a violation of 3NF since there is partial key dependency, right?
(I haven’t had this debate in like 5 years, so I don’t have any good cases for you to disprove?!)
Not storing them separately would mean that your database isn’t normalised because combining them together implies a relationship between the addresses that does not exist.
I agree, but was just using that as an example of people who are holding on a little too “tightly” to the “theory” of database design!
(Wish I had know you guys 5-6 years ago when I had all of this stuff in the forefront of my mind, had better examples, and had better debates going where I needed backup!
Anyways… I think I get it, and you and r937 seem to think I’m on track which is reassuring.
storing “address given at time of order” in with the order is ~not~ a violation of 3NF
Can you give me a more theoretical reason in case I ever need to defend that design decision? (I’ve seen A LOT of supposed “DB gurus” shoot ideas down like that cold. That along with an incessant insistence on using “AutoNumbers”?!)
I would say that the Billing Address, Shipping Address, Ship To Name, Credit Card Name, Credit Card Last-4, Sub-Total, Sales Tax, Shipping Type, Grand Total, and Sale Unit Price (actually stored in the “Order Details” table) all pertain to a specific Order in the space-time continuum, and therefore define a “unique” record.
A Customer could place 10 Orders and have 10 different Billing/Shipping Address combos, so you must capture that info in the Order table just like you have to store the Order Date/Time in each record.
Not sure if my logic is correct?!
As far as storing other Address types, do you also agree with what I said?
Would I either want to store the “current” Contact/Permanent Address in the Customer table, or should I create an “Order History” table of Contact/Permanent Addresses for a Customer over time?
Our goal is to not be intrusive like most mega-corporations, however, we do need to store enough address information if there was ever a case of fraud, non-delivery, or some other legal issue.
anybody who tells you that you should always use an auto_increment is, almost by definition, most assuredly ~not~ a database guru
you’re well on the right track
as for the 3NF stuff, i suggest you look up the definition of 3NF (you can easily find half a dozen different versions, none consistent with the others, but in a nutshell, 3NF deals with the relationship of non-key attributes to the key), and i’m sure you can bend at least one of these definitions to your situation to prove that storing the customer data at time of order into the order table is not in violation
note that 3NF says nothing about redundancy or repetition, which is what many people claim they are trying to avoid when they go splitting stuff off into separate tables with auto_increments
Okay, so it seems like I was on track with Billing and Shipping Address. And also with creating an “Address Book” where Customers can add/delete Billing and Shipping Addresses as they see fit.
My last question then is, “What is the best way to handle a ‘Contact Address’ for the Customer?”
I suppose a Billing Address is often where a Customer’s lives, but then again, many people have P.O. Boxes.
Do companies ever ask for “Permanent Street Addresses” for e-commerce, or is that too intrusive and excessive?
Also, shouldn’t I keep a “Billing Address” permanently on file for the Customer, like I’m permanently storing the Billing Address and Shipping Address for the Order?