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.
Best Answer
In the past, what I've seen at companies that do this is one of two approaches:
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.
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.