Storing combinations of item properties in database

Question

I have a problem of such:

Let's say I have an item, a CUP for example. I want to sell it, but want to allow the user to pick CUP properties, such as Size, Color, Material. When the user will select Size (maybe Large), color (maybe Black) and Material (maybe Glass) then I need to show him, that we have 20 such Cups in warehouse and the cost is $25 each. And now: I don't know how to store those combinations in database.

Here is my ultra stupid solution:

For each combination I will have a column, yet, adding any new combination might be painfull as well, as removing some, I will have to map them somehow, well...

Id | Product Name | LargeBlackGlassPrice | LargeBlackGlassCount | SmallBlackGlassPrice | SmallBlackGlassCount | Medium...

stupid idea, but as for now didn't hit anything better :/

Hope it's clear what I want to achieve. Thank you

Answer 1

Consider the following ERD:

The system administrator maintains a list of product categories, these may include, for example, cups. The administrator also maintains a list of features. These could include size, colour, material, and anything else that they decide is potentially important for any type of product. The administrator can then create an intersection of categories and features to indicate which features matter for a particular product category.

This establishes the "rules" for a catalogue of products. Which types of products do you have and what is important to know about each of these types products.

Now, to store the products themselves, you have the SKU table. Each individual product, for example: Large Black Glass Cups is stored in this table. You can store the current price of this product here. You can also store the stock on hand here, although I've recommended elsewhere to never store stock quantity directly. Inventory management is not the basis of your question, however.

For any particular product (SKU) you then have a list of product features where the specific values of each specific product are stored. The features that matter are the ones defined by the product's category as listed in the CATEGORY_FEATURE table.

On your website, when a customer is searching for items in a PRODUCT_CATEGORY, (e.g. Cups) you show them the list of CATEGORY_FEATUREs that apply. For each feature, you can create a drop down list of possible values to choose from by using:

select distinct PF.value
from CATEGORY_FEATURE CF
  inner join PRODUCT_FEATURE PF
    on  CF.product_category_id = PF.product_category_id
    and CF.feature_id = PF.feature_id
where CF.product_category_id = CategoryOfInterest
  and CF.feature_id = FeatureOfInterest
order by
  PF.value

This design gives your administrator the ability to define new product categories and product features without having to make database schema or code changes.

Many people are likely to point out that this design uses the Entity-Attribute-Value (EAV) pattern, and they are equally likely to point out that EAV is EVIL. I agree in principle that EAV is to be avoided in almost all cases, but I have also asserted that in some cases, and in particular in the case of product catalogues, EAV is actually the preferred design.

Answer 2

Table1 => Cup Master

Fields => Cup Id | Product Name

Example =>

1001 | CUP A

1002 | CUP B

Table2 => Property Master

Fields => Property_Id | Properties

Example =>

1 | LargeBlackGlass

2 | SmallBlackGlass

3 | MediumBlackGlass

Table3 => Inventory Master

Fields => Cup Id | Property_Id | count | price_per_piece

Example =>

CUP A | 1 | 3 | 45/=

CUP A | 2 | 2 | 40/=

CUP A | 3 | 2 | 35/=

CUP A | 1 | 3 | 45/=

CUP A | 2 | 2 | 40/=

NOTE: A cup with a particular property might be available and with other property might not.

Answer 3

Let try to reason how to solve your task. I will describe general conception and split it in some steps:

1. Define types of products that you are going to sell: cup, plate, pan and so on. Create table products with fields: id, name, price.
2. Define colours of products: black, red, brown. Create table products_colours with fields: id, name, price.
3. Define sizes of products: small, medium, large. Create table products_sizes with fields: id, name, price.
4. In simple case all types of products will have the same price and will store in table products.
5. In simple case additional price for colours and sizes will be the same for all types of products and will be stored in tables products_colours and products_sizes.
6. Create table customers_products with fields: id, products_id, products_colours_id, products_sizes_id, quantity.
7. Write a query for join all table together to fetch all products with colours, sizes and all prices from db.
8. In the script iterates through all rows and calculate price for every product as a sum of product price, size price and colour price.

To sum up: this is very basic implementation that doesn't include things like brands, discounts and so on. However, it gives you understanding how to scale your system in case of adding additional attributes that affect the final price of products.