5

Say I have an e-commerce with a data model like the following, which allows a Sale to describe how a single Product is sold. This division allows a product to be sold multiple times in different ways (different pricing, or offered at a different point in time). This works great when a sale is selling a single product.

Sale

  • product_id (sale belongs to product)
  • other offer details like price, sale expiry date, marketing content, etc

Product

  • product has many sales
  • factual product details like name, photo, manufacturers description, etc

The problem: How can this data model be improved to allow a sale to have many products?

For example, to create a bundle of 1 of product A, and 1 of product B. So if a customer wants quantity=3 of those bundles, they would receive a total of 3 A's and 3 B's.

Or a sale could be for multiples of a single product. In this case the sale would be for 5 of product C. So if a customer wants quantity=3 of those bundles, they would receive 15 product C's.

The best idea I have so far is add a new table. This would allow a sale to reference many orderables, which describe what's in the bundle.

Orderables

  • belongs to product
  • belongs to sale
  • qty in the bundle

But a refactoring of this magnitude is a monumental task. The number times sale.product appears in the codebase for a variety of reasons is enormous. And every single case need to be fixed in code, and probably presentation as well.

So before we commit too heavily to this approach, I'm wondering if there is a glimmer of hope I've not yet seen, or if my future is fraught with the pain and peril of this epic refactoring.

2
  • 1
    What you're doing sounds not unlike most designs for any kind of ordering/invoicing system, in that each line item on the invoice will typically contain all details of the original product being sold in addition to the product_id (eg, you copy the price, description, SKU, etc). This is so you can later generate the same invoice, even if the price or anything else has changed since the original sale. Take a look at the Northwind sample database for an example of Order->Order details->Product schema. You can probably use that design as a basis for yours.
    – gregmac
    Commented Feb 7, 2014 at 2:21
  • We do that for orders, which have line items that link to sales. But I think the separation of product and sale is somewhat non standard here.
    – Alex Wayne
    Commented Feb 7, 2014 at 6:14

1 Answer 1

Reset to default
5

In the past, what I've seen at companies that do this is one of two approaches:

  1. A "bundle" is a separate product. It's put into the database as its own product, stored in inventory as its own product, and gets removed from inventory and shipped as its own product. No refactoring required, but ties up product in inventory that could be sold individually, if it was broken out. It has the virtue of being customizable as a bundle; i.e. the cost can be different than the individual products.

  2. A "bundle" is a "Bill of Materials," culminating in a finished product. Requires a completely different approach, and much refactoring, but preserves the integrity of individual products on shelves. There would be lots of duplication, since each individual product still needs its own bill of materials containing one item (itself).

If I were doing it, I would find a way to sell the idea of bundles as "quick-entries." It works sort of like a GUI macro; you select a bundle from the list of products, and it automatically adds the necessary individual line items (and quantities) to the invoice. You still need a "bill of materials" to implement it but you don't have to refactor the entire system.

1
  • 1
    Option 1 is a decent compromise, but inventory does greatly complicate it. Bundled products may not be acquired as bundles. Thanks for the ideas!
    – Alex Wayne
    Commented Feb 7, 2014 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.