Managing Multiple Billing / Shipping Addresses - Order System

versatilewt

I've searched around but always end up finding things on email addresses. This is a design issue more than a coding issue. I'm working on an order system / shopping-cart-esque system and am at the point where I'm dealing with managing shipping and billing addresses (mySQL database). We are likely to have repeat customers, as well as many one time customers. I've been thinking about this issue for some time now.

I'm all about normalizing databases, and I want it to be convenient for customers to store multiple addresses (shipping and billing) to make ordering easier, and to have optimal database performance. Naturally, a problem would arise for historical purposes if somebody places an order on 7/24/06, and updates their address on 7/25/06, thereby reflecting the wrong address on the 7/24/06 order. So, here's what I had in mind; let me know if this is good or if you have had any other experiences managing addresses.

I plan to have a table for addresses, with a FK to the customer ID, a PK address_id, a flag for active/inactive, the usual address info, and a "nickname" for easy reference to the address. Then, users can add as many addresses as they like. If a user were to update an address, it would mark the old address as inactive and then insert a new address with the updated information. Is this the best way?

Also, do you find it best to tie in billing information directly with a billing address in the same table, or better to have shipping and billing addresses in one table, with a billing/shipping flag, and then link billing addr and billing info? I know some sites like buy.com associate a payment method directly with a billing address, and am not sure the best way to go about it.

Thanks for the help!

jheitz

About the address change issue:

I would suggest you have validy periods for addresses instead of inactivating them. Basically the same approach you are suggesting but, instead of just inactivating the address you have an "End Date" for that address. That way, the active address would be the one without and "End Date" and you will have an history of the users' addresses through time.

As for the billing info, I would go for your second suggestion, to have a flag in the address table (to determine id this is a billing or shipping address) and then link the billing info that should be stored in the same table. On second thought, I would have 2 flags on the address table, as an address can be both billing and shipping.

Bear in mind that I have never worked on systems like these, just giving you my point of view in case you find it useful. Good luck

MarkR

Don't store addresses (exclusively) in a separate table from orders. Each order's addresses must be set at the time of the order and never changed, even if the user changes their address subsequently.

i.e. ensure that the address(es) used is stored forever and cannot be changed.

You can still have an address book, but copy the chosen address into the order rather than putting a FK in.

Mark

versatilewt

Mark,
Thanks for the reply. My only issue with that is storing unnecessary data. I'm trying to keep the database size down, and I could see repeat customers having their address stored over and over. I haven't had much experience with this, so I'm not sure if it's much of an issue.

Do you see a major problem with the idea of having an "active/inactive" flag, and if a user wants to "edit" an address, what they're really doing is marking the address that has been used in a previous order as inactive (for their address book purposes) and creating a new address?

MarkR

versatilewt wrote:
Mark,
Thanks for the reply. My only issue with that is storing unnecessary data.

The address of a previous order is NOT unnecessary.

I'm trying to keep the database size down, and I could see repeat customers having their address stored over and over. I haven't had much experience with this, so I'm not sure if it's much of an issue.

Keeping the database size down is a pointless thing to do in this context. You absolutely NEED to store this information per order. There is absolutely no way it can be allowed to change- it's just wrong.

Likewise, product names, prices etc, need to be stored on the order with the SKUs so that if they subsequently change (or are deleted etc), you can see what the customer really bought.

Do you see a major problem with the idea of having an "active/inactive" flag, and if a user wants to "edit" an address, what they're really doing is marking the address that has been used in a previous order as inactive (for their address book purposes) and creating a new address?

Unnecessarily complicated. An order will typically have exactly two addresses, shipping and billing. No order has less than this (although they could be the same) or more. Therefore, there's no need to store them in a separate table at all.

For reference, our order_head table looks like this:

CREATE TABLE `order_head` (
  `id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `created_date` datetime NOT NULL,
  `confirmed_date` datetime default NULL,
  `language_code` char(2) NOT NULL,
  `billing_title` varchar(5) default NULL,
  `billing_name` varchar(90) default NULL,
  `billing_email` varchar(50) default NULL,
  `billing_address` text,
  `billing_postcode` varchar(20) default NULL,
  `billing_country_code` char(2) default NULL,
  `billing_country` varchar(30) default NULL,
  `billing_telephone` varchar(30) default NULL,
  `billing_fax` varchar(30) default NULL,
  `shipping_title` varchar(5) default NULL,
  `shipping_name` varchar(90) default NULL,
  `shipping_address` text,
  `shipping_postcode` varchar(20) default NULL,
  `shipping_country_code` char(2) default NULL,
  `shipping_country` varchar(30) default NULL,
  `shipping_method_index` int(11) NOT NULL default '0',
  `total` decimal(10,2) NOT NULL default '0.00',
  `payment_total` decimal(10,2) NOT NULL default '0.00',
  `payment_refunds` decimal(10,2) NOT NULL default '0.00',
  `payment_module_code` varchar(10) default NULL,
  `payment_module_name` varchar(100) default NULL,
  `payment_info` blob,
  `http_headers` text,
  `remote_address` varchar(16) default NULL,
  `affiliate_id` int(11) default NULL,
  `affiliate_credited_amount` decimal(10,2) default '0.00',
  `affiliate_commission` decimal(2,2) default NULL,
  `original_referrer` varchar(200) default NULL,
  `discount_code` varchar(10) default NULL,
  `comments` text,
  `payment_attempted` tinyint(4) NOT NULL default '0',
  `last_payment_attempt_date` datetime default NULL,
  `original_billing_title` varchar(5) default NULL,
  `original_billing_name` varchar(90) default NULL,
  `original_billing_email` varchar(50) default NULL,
  `original_billing_address` text,
  `original_billing_postcode` varchar(20) default NULL,
  `original_shipping_title` varchar(5) default NULL,
  `original_shipping_name` varchar(90) default NULL,
  `original_shipping_address` text,
  `original_shipping_postcode` varchar(20) default NULL,
  `is_gift` tinyint(4) NOT NULL default '0',
  `gift_message` text,
  PRIMARY KEY  (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `status` (`status`),
  KEY `created_date` (`created_date`),
  KEY `ix_order_head_payment_attempted` (`payment_attempted`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Yes, 49 columns. We have considered at every point splitting it up and never found a good reason to do so.

Mark

versatilewt

Mark,

The address of a previous order is NOT unnecessary.

I didn't mean that the previous data was unnecessary; I mean "redundant" more than unnecessary (e.g. if a customer orders over and over to the same billing/shipping addresses).

Keeping the database size down is a pointless thing to do in this context. You absolutely NEED to store this information per order. There is absolutely no way it can be allowed to change- it's just wrong.

The "flags" would have prevented an address from changing, by inactivating and creating a new one. I was thinking about the inactive/active flags everywhere and it does seem a bit complicated (and error-prone). With this (creating new addresses whenever a customer updates), and the relatively small size that the addresses will take up, the more I think about it, the less significant the data storage seems. Plus I can see this certainly reducing the server load for querying the billing/shipping addresses whenever viewing an order.

Anyway, I do agree with you (this was how I initially designed, with addresses in the order table). I'll just store an address book in a separate table for customer convenience and place the addresses directly in the order.

Thanks for the advice.