Chris 8:34 pm on November 30, 2012  

Develop For The Future

Let’s say we start a specialized e-commerce site that sells fishing lures that are dropped shipped from Henry’s, a supplier. We found a good deal with Henry’s and we’re so excited to start generating revenue and quite our day jobs a pencil pushers. To get this site up and running we create some database tables:

  • HenrysProducts
    • SKU
    • Brand
    • Model
    • Price
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderID
    • SKU
    • Quantity
    • Price

You slap up a few files:

  • index.cfm
  • products.cfm
  • cart.cfm
  • shippingInfo.cfm
  • paymentInfo.cfm
  • orderConfirmation.cfm
  • contactUs.cfm

Put in some images and “bam” you’re rolling. The lures you’re selling are “hot stuff” and you start bringing in revenue. Yea!

Oh! What’s this? An order for $500 worth of lures? Yes!

The order goes through, but Henry’s says “Sorry, we don’t have 100 of the 500 lures you ordered. We’ll place those on back-order for you.”. You think, “Okay, no problem.”. But wait! How are we going to handle that? Time for a new table and some logic to handle emails when things are backordered, cancelled and shipped:

  • HenrysOrderFulfilment
    • OrderID
    • SKU
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • HenrysShipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier

Now that we’ve started getting popular, people really want some items that are only available from the manufacturer. But now we have everything setup for Henry’s, not thinking ahead for the future. So we have a few options:

  1. Keep using what we have and append a column to tell us where it’s really coming from
  2. Create new tables for the new manufacturer
  3. Refactor the tables to be more generic

And of course, we should opt for #3. This should have been where we started in the first place. You should never assume your business model will never change, your contract might never expire from your supplier or that your business will never grow. Because with that kind of thinking, it becomes much more costly to grow your business having to deal with the side affects of your starting decisions. That growing time also usually involves lack of time because you become so busy with the messy system. Here’s an example of how it should have been built in the first place.

  • Products
    • ProductID
    • Brand
    • Title
  • ProductChildren
    • ProductChildID
    • ProductID
    • ModelTitle
    • SKU
    • UPC
    • Price
    • Weight
  • ProductSupplierLookup
    • ProductChildID
    • SupplierID
    • Cost
    • Priority
  • Suppliers
    • SupplierID
    • Name
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderItemID
    • OrderID
    • SKU
    • Quantity
    • Price
  • OrderFulfilment
    • OrderItemID
    • SupplierID
    • ItemCost
    • QuantityOrdered
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • Shipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier
    • Method

With this type of structure you can add multiple supplier, order from multiple suppliers for each customer order using a priority number, handle back-orders and cancellations, use the most cost efficient supplier, change, add and remove suppliers and keep track of multiple packages shipped.

Of course this is a fairly basic structure, but it’s a good way of thinking forward whether you’re starting from scratch or enhancing your system.

That touches the SQL aspect of it. However if you re-factored your code using the above file structure, I’m betting you’re code is complex and messy. You may have even coded it a year ago and forgot where everything was. You may even have a different developer or developers working on the enhancement.

This is why you should always split out your view code into .CSS, .JS and template files to generate HTML. You should also be using CFC’s, if using ColdFusion, and a FrameWork is a must. You now have a much more manageable file structure that you can manipulate your code with much more ease. Also don’t forget your unit testing so you reduce your chances of accidentally breaking something. I suggest using something like FW/1 as it’s lightweight and easy, one you get the hang of it.