0

This question is related to Database Design: inventory and sales system?

So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it can be customized by adding/removing/modifying components.

How is this customization accounted for as a line item? Would there be another table ProductCustomization with a reference to the Product and the Component and a column to indicate the type of customization? What about if you want to modify the Composition of a Component in the Product (ex. 3 pickles per burger).

An example of a customization would be ordering a burger with no pickles, or adding extra cheese.

Community
  • 1
  • 1
MickB
  • 265
  • 1
  • 4
  • 11
  • Maybe I'm missing something, but why isn't Product and Component enough? A customizable Product is still a Product. In your example, you have a Product burger with extra cheese. – Gilbert Le Blanc Jun 22 '16 at 13:21
  • Right, but for every component there would be a need to add a number of products to account for every possible customization. Also, it does not seem practical to have, for example, three burgers listed in a product just to vary the amount of pickles. – MickB Jun 22 '16 at 13:24
  • Ok, your burger Product will consist of multiple rows in the Product table. Each row consists of a Product ID, Component ID, and quantity. Your Component table will have a Component ID, Component Name (pickle), and other fields that make up the component. – Gilbert Le Blanc Jun 22 '16 at 13:31
  • Would that appear as a completely new product on a line item? Also would I would still have account for any tiny customization? One could easily be missed and then a customer couldn't order the burger the way they want it. – MickB Jun 22 '16 at 13:36

2 Answers2

0

I though of a solution as follows :

There will be one product table.

Product(pid , description).

So burger will go in product table. It will have data as (Burger , "Bun burger")

There will be different table for component which will save all possible variations of each component.

Components (TypeId , Description)

So each component will save its all possible types with description. like CHEESE will have values as (CHEESE_EX, ExtraCheese) (CHEESE_N, Normal Cheese) etc

I will have one more table for custamized product as

Custom_product(Custom_prod_Name, pid, typeId, component_quantity)

We can compose the product as we want using Custom_product table. Suppose we want custom_burg_1 to be created then for each component to be added in this burger there will be one row in Custom_product table with same custom_prod_name as custom_burg_1.

So Custom_product table will look like :

(custom_burg_1, burger , BUN_N,1 )

(custom_burg_1, burger , MEAT_N,1 )

(custom_burg_1, burger , CHEESE_EX,1 )

(custom_burg_1, burger , PICKLES_N,2 )

(custom_burg_2, burger , BUN_N,1 )

(custom_burg_2, burger , MEAT_SP,1 )

(custom_burg_2, burger , CHEESE_N,1 )

(custom_burg_2, burger , PICKLES_N,3 )

So using this values you can check composition of both custom_burg_1 and custom_burg_1. Also we can modify each component and its quantity in burger.

Jyoti mishra
  • 597
  • 4
  • 16
ketaki shah
  • 26
  • 3
0

If you followed the ERD as you linked in your initial question. I would make SKU's for removal or addition of components. The amount of a certain component would be negative or positive depending on what you pick.

ie.

SKU: No Pickles price -0.50

SKU: XTRA Pickles price +0.50

you could add that to any SALE, your inventory should be correct, and your price of the sale would be correct too.

Whacko
  • 101
  • 1