Database Design: Compilation of Products and Products

Question

Let's say I sell several product . Sometimes a product is a combination of other product . For example, let's say I sell:

• Hot Dog
• soda
• hot dog + soda combo

How do I simulate something like this? Should I have a product table for listing individual products, and then a product_combo table that describes combos, and another table related to product and product_combo to drill down products in combos? It seems simple to me.

However, what if I want to record all sales in one table? Meaning, I do not want the product_sales table and the product_combo_sales table. I want all sales to be in one table. I'm a little unsure how to model product and product teams in such a way that I can later record all sales in one table.

Suggestions?

NOTE. I am wondering if I can put the compilation of the product and product into one table using parent-child relationships. With one table, selling sales will not be difficult. I just would have to implement a business rule editing a combo product when the sale is already recorded against this combo, which in fact the change leads to a new record. However, it can be random.

Answer 1

It depends on what you really need to do with your system. A system that needs to track inventory will need to understand that a “combination meal” must debit an inventory of one hot dog and 32 ounces of soda (or something else). However, a system that only tracks orders and dollars does not really care about what is “included” in the combination food — only that you sold it and paid for it.

However, suppose you need an inventory system. You can reduce your complexity by slightly changing your definition. Think in terms of (1) inventory items and (2) menu items. The inventory_items table contains items that you buy and track as inventory (hot dogs, soda, etc.). The menu_items table contains the items you sell (Big Dog Combo Meal, Hot Dog (sandwich only), etc.).

You can have some menu items that, by coincidence, have the same name as the inventory item, but for these menu items they are treated the same way as you do a combined item and insert one entry into the link 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 build this example, it turns out that even hot dogs have two components (you have to count the bun), and not just one. To come up with the simplest case (a menu item with one component), I added Soda to the menu. However, think that if you are going to inventory non-food items (cups), then even plain soda will have two components (three if you are an inventory of straws).

Please note that with this design there will be no special code pages for processing combo elements and non-combo elements. All menu-related functionality will use only the menu_items table, all functions related to inventory and cooking will be JOIN menu_items before recipes and (if additional fields are needed) before inventory_items .

You will need special treatment for additional components (sauerkraut, grease, chili, etc.) and for components that can be selected from different inventory items (presented as * soda in this model), but this should help get you started.

Answer 2

I suggest you think in terms of “orders” and “points”. An order consists of many items. Elements can be different "products." So, examples of Orders can be:

1) hot dog

2) soda

3) hot dog + soda

Examples of Elements can be:

A) hot dog

B) soda

You can also save sales in the order table. I do not think that you need to have advantages for "combos" in your database. This is the business logic that should be applied in code, not in the database. You can apply all your discounts later in the code.

Answer 3

Both approaches are normal. But there is at least one more way to solve the problem, which is to apply discounts to product combinations (which means that you can also selectively distribute the discount), for example.

 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 keep in mind that you will need to use the procedural code to assign sales discounts (and the flag of each item sold along the way) to solve the problem of whoever buys 2 hot dogs and one soda (and therefore only gets one discount )

... and therefore the total sale value 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=?