Database Design: product and product combo

Question

Say I am selling a number of product. Sometimes, the product is actually a combination of other product. For example, say I am selling a:

• hot dog
• soda
• hot dog + soda combo

How should I model something like this? Should I have a product table to list the individual products, then a product_combo table that describes the combo, and another table that is associated with product and product_combo to itemize the products in the combo? This seems straightforward to me.

However, what if I wanted to record all the sales in one table? Meaning, I don't want product_sales table and a product_combo_sales table. I want all sales to be in just one table. I'm a bit unsure how to model product and product combos in such a way I can later record all sales in one table.

Suggestions?

NOTE: I'm wondering if I could put product and product combo in one table using a parent-child relationship. With one table, then recording sales won't be hard. I'd just have to implement a business rule that editing a product combo when a sale is already recorded against that combo that the edit actually results in a new entry. Could get messy, though.

Answer 1

This depends on what you actually need to do with your system. A system that needs to track inventory is going to need to understand that a "combo meal" needs to debit the inventory by one hot dog and 32 ounces of soda (or whatever). A system that only keeps track of orders and dollars, however, doesn't really care about what "goes into" the combo meal -- only that you sold one and got paid for it.

That said, let's assume you need the inventory system. You can reduce your complexity by changing your definition a little bit. Think in terms of (1) inventory items and (2) menu items. Your inventory_items table contains items that you purchase and track as inventory (hot dogs, soda, etc). Your menu_items table contains items that you sell (Big Dog Combo Meal, Hot Dog (sandwich only), etc).

You can have some menu items that, coincidentally, have the same name as an inventory item but for these menu items treat them the same way you do a combo item and stick a single record into the linking table:

 inventory_items            menu_items       recipes (menu_item, inventory, qty)
 ---------------            ------------     ----------
 hot dog                    Hot Dog          Hot Dog, hot dog, 1
 hot dog bun                Hamburger        Hot Dog, hot dog bun, 1
 hamburger patty (4oz)      Big Dog Combo    Hamburger, hamburger patty (4oz), 1
 hamburger bun              Soda (32oz)      Hamburger, hamburger bun, 1
 cola                                        Big Dog Combo, hot dog, 1
 ginger ale                                  Big Dog Combo, hot dog bun, 1
                                             Big Dog Combo, *soda, 32
                                             Soda (32oz), *soda, 32

Just constructing this example, it turns out that even the lowly hot dog has two components (you have to count the bun), not just one. To come up with the simplest case (a menu item with a single component), I added Soda to the menu. Consider, however, that if you are going to inventory non-food items (cups) then even a simple Soda is going to have two components (three if you're inventorying the straws).

Note that with this design there will be no special codepaths for handling combo items and non-combo items. All menu-related functionality will use only the menu_items table, all inventory and food-prep related functionality will JOIN menu_items to recipes and (if additional fields are needed) to inventory_items.

You'll need special handling for optional components (sauerkraut, relish, chili, etc) and for components that can be selected from different inventory items (represented as *soda in this model), but this should get you started.

Answer 2

Both you're approaches are OK. But there's at least one other way to solve the problem which is to apply discounts to product combinations (which means you can also apportion the discount selectively) e.g.

CREATE TABLE products
(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(128),
  description TEXT,
  price INT
);
CREATE TABLE combo_discounts
(
  id NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(128),
  description TEXT
);
CREATE TABLE cd_products
(
  cd_id INT /* REFERENCES combo_discounts.id */,
  p_id INT /* REFERENCES product.id */
  price_reduction INT
);
CREATE TABLE sales
(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  location ...whatever...
);
CREATE TABLE sales_items
(
  sale_id INT /* REFERENCES sales.id */
  p_id INT /* REFERENCES product.id */
  cd_discount INT /* REFERENCES cd_products.cd_id */
);

But bear in mind that you'll need to use procedural code to assign the discounts the sale (and flag each sold item as you go) to address the problem of someone buying 2 hot dogs and one soda (and hence only getting one discount).

...and hence the total price for a sale is

SELECT SUM(p.price)-SUM(cd.price_reduction)
FROM sales s INNER JOIN sales_items si ON (si.sale_id=s.id)
   LEFT JOIN cd_products cdp ON (si.cd_discount = cdp.cd_id
      AND si.p_id=cdp.p_id)
AND s.id=?