E-commerce – Orders and Addresses
Introducing the Problem
In many e-commerce applications, you often want to associate an order to two addresses – a billing address and a shipping address. On the OO side, this is easy. You create an order which has-one BillingAddress and has-one ShippingAddress. Often in simple systems BillingAddress and ShippingAddress will just both be instances of an Address object, although as the system becomes more complex,you might want to have a BillingAddress and ShippingAddress object that subclass the Address object and provide shipping or billing specific methods and/or validations.
An OO Approach
If you were an OO programmer using a default ORM approach, you might persist your data to a tbl_Order and a tbl_Address (I don't personally prefix my db tables with tbl_,but I find it's a useful convention when blogging to use a Hungarian notation approach to make it clear I'm talking about db tables). This actually isn't a bad model as with non-trivial e-commerce apps you'll often provide your end users with the ability to store and re-use addresses, so an address table can be quite a handy thing to have.
The problem comes if you allow a user to edit their addresses through a “my account” feature. What happens if they edit an address that has been associated to an order that has already been shipped? Because you want to keep a historic record of the actual address the order was shipped to, you either need to implement versioning based solution where the order is associated to a specific version of an address and the historic addresses aren't deleted when a new version is approved, or you're going to have to have a separate copy of the addresses elsewhere (perhaps directly in the order table) to ensure the integrity of your order data.
A Procedural Perspective
Someone coming from a procedural/relational background might wonder what all the fuss is. Why not just add BillAddress1, BillZip, and the like to the order table and just treat these as attributes of the order object - if we really need to have objects at all?!
What's the Best Approach?
There are real benefits to an Order being composed of Address objects. It means that you can describe validation rules for addresses and can then reuse those throughout the system. The problem is how to handle the persistence concern.
While I have a versioning based cms, the idea of requiring versioning as a precondition for being able to solve this (relatively simple) problem seems inelegant to me. I'm also concerned about having to overlay special version persistence rules on the Address object and about the mayhem that would ensue if I ever changed those version persistence rules without realizing that it was an implicit dependency that my Address objects had.
I am wondering instead whether it might not be better to add the concept of a Copy attribute so you could Describe Order.BillAddress1 as a Copy of Order.BillAddress.Address1. That would provide a mechanism for persisting the billing address directly in the order table without having to write any custom code (providing a data structure that would simplify exports and speed search queries) and that wouldn't require a versioning based solution to work.
What do you think? How would you generally solve the class of problems where an object needs to store data from a related object into the primary table it is persisted in? Also, would you consider the possibility of having “virtual” objects that provide all of the encapsulation benefits of an object but that are persisted as fields in the table of the object they are composed into? That would be a way of solving the problem for cases where (unlike the addresses with the potential value of an address book system) a separate table for persisting the object is not necessary. A convention based approach might suggest that composing an Order with an Address named “Bill” address would automatically add a BillAddress1, BillAddress2, BillCity, etc to the Order table and should we later extend the Address object to have a CountryCode property, then BillCountryCode would automatically be added to the Order table on the next validation/regeneration of the db model.
Any thoughts appreciated . . .
I think that your order refs your addresses, ultimately, via a foreign keys to the addresses table. In similar fashion, a user's addresses are refed via foreign keys to the addresses table. When viewing a new order the address info would come from the records refed by the user, unlike an existing order, which would be refed by the order itself. Addresses tied to an existing order shouldn't be editable, though those associated with a user should.
After that it comes down to process. When a user attempts to change/update an address the first thing will need to be to determined is if that address is already associated with any other records in the db (this could be orders, shipping, all kinds of stuff). I an address is already associated elsewhere then an 'update' would actually be a new address to the database, with a change to the foreign key in the user's table.
Another way to handle this, that might be less intensive on the database, is to set a 'false' default boolean 'blOutsideMarker' field to the address record. When that address is referenced elsewhere (orders, shipping, whatever) for the first time outside of it's original context then the boolean is flipped to 'true', which then denotes in the future that the record can not be edited due to cross-referencing.
It's only a partial solution. It is useful as it allows you to store the details with the order (which is necessary), but it doesn't really work if you also want to provide address book functionality for returning clients. You could select distinct addresses, but that doesn't allow users to add, edit or delete addresses, so a comprehensive system needs to store them in both the order table and an address table.