Jump to content

Archived

This topic is now archived and is closed to further replies.

versatilewt

Managing Multiple Billing / Shipping Addresses - Order System

Recommended Posts

I've searched around but always end up finding things on email addresses. This is a design issue that I have, not an actual coding problem. 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 and new PK. 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!

Share this post


Link to post
Share on other sites
Well, yes, you should link the address_id to the order, but you only need to add a new address if the current ones are being used already.  I would also recommend keeping all the address info in one table, with a flag, as you suggest.

Share this post


Link to post
Share on other sites
I had thought about that needing to add a new address only if the current one is in use. Perhaps another flag would be in order, "in_use" or something? And then have the basic if

if(in_use)
  mark old as inactive, create new listing
else
update existing

?

Share this post


Link to post
Share on other sites
Definitely _not_ another flag... simply query the orders table for that address_uid in real-time.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.