SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Advice on storing historical order address info

    I'm storing data about orders and associated addresses (billing/shipping). Say we associate the address with the order with address_id. The address would also be associated with the customer, so they can select from their list of addresses next time they order.

    Say we allow a customer to add a new address to their account for a new order. We create a new address entry and associate it to the new order.

    But if we allow the customer to make a small change/correction to their address for future orders. We don't want to just update the existing address entry because we want to know exactly where previous orders were shipped. But if we just create a new address entry, then the customer could have some near-identical addresses on their account - looks a bit messy.

    Possibly for adding an address, we add the address entry, and associate it with the customer, and with any subsequent orders for which that address is selected. And for a update of address, add a new entry, and remove the association of the old address from the customer, but then it stays associated with previous orders.

    Just wondering if anyone else had come across this situation and how they dealt with it.

  2. #2
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    I would keep all this simple :

    A table of adresses, so that a client can enter has much address he wants. You also place a boolean value for if the address is default shipping or billing :

    Code:
    CREATE TABLES address(
        id INT NOT NULL AUTO_INCREMENT,
        clientID INT,
        zipCode VARCHAR(5),
        ...
        isDefaultShipping TINYINT(1) DEFAULT 0,
        isDefaultBilling TINYINT(1) DEFAULT 0,
        PRIMARY KEY(id)
    )

    Then for each order, keep the Shipping and biliing addresses. You will duplicate a lot of data. But even if you make more then a million shipment a year it will only take a few MB.

    Goob luck

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    hmmm. Yeah I know the duplication wouldn't be much of a storage problem, but it means that, from the point of view of offering the customer to choose from their previous addresses, you can do either:
    -Offer only the default address(es).
    -Offer any DISTINCT address from the list of used addresses. If small changes have been made to addresses, this could result in a list of very similar, but distinct addresses.

    So yeah,it's a possible solution. And it is pretty simple. Just wondered if anyone had another to take into consideration?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    let the customer define as many addresses as they want, and call them whatever they wat: home, work, girlfriend #1, girlfriend #2, etc., but only let them choose from the addresses they've already defined (not the ones they've used before).

    when an order is committed, record the complete address used in the order record.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was in an extremely large retail chain the other day and the appliance salesperson pulled up about 12 records for a customer in front of me. He stated there were so many records because the smallest change in address/phone number created a new record.

    So, at least one(gigantic) company stores them all and just plops the choices onto the screen, and makes the user pick one. This would probably be a nightmare for a customer who maintains apartment complexes, and so maybe has hundreds of unique addresses due to shipping to different apartment numbers.

    You could maybe sort the results in an attempt at relevance. Like maybe the date the record was added(newer may equal better) or the date the record was most recently used to place an order. Or maybe how many times the record was used to place an order. I think the best method will depend on your specific context.

  6. #6
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    when an order is committed, record the complete address used in the order record.
    Do you mean just store the address as text? That's kind of what I've already inherited, but it seems a bit, you know, 'un-databasey'.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    ... but it seems a bit, you know, 'un-databasey'.
    my vote for cool new word of the week

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    FINALLY. an award.

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, it seems undatabasey, but if you need to store the exact address used when creating an order for historical purposes, then so be it.

    another example that might seem slightly less undatabasey but is still exactly the same concept is storing the actual purchase price of an item when you commit an order. that way you can always recreate a receipt/invoice with the actual price the customer paid, even if the price of the item changes.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck View Post
    another example that might seem slightly less undatabasey but is still exactly the same concept is storing the actual purchase price of an item when you commit an order.
    indeed

    The idea is that when the customer makes an order your store has entered in an agreement to sell something at a particular price. That price is stored on the order and is now a fact about that order. It is not redundant because you are not storing the SKU's generic price, you are only storing the price that that customer is going to pay on this order.
    -- The Database Programmer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Fair point about the price.

    I did think you were saying to store the address in a single text field, but now I'm not sure. Basically my mind went there because that's the system we've got at present - a single text field. Which obviously makes it more work than necessary to, say, extract the postcode from an order. But if the address were split into its logical sections (line1, line2, town, poostcode etc) then it seems less undatabasey to store it directly on the order.

    By the way, I'm glad my word is catching on!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •