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.
- product_id (sale belongs to product)
- other offer details like price, sale expiry date, marketing content, etc
- 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.
- 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.