Product attributes db structure for e-commerce

user_1236535 0
Viewed 3578

Backstory:

I'm building an e-commerce web app (online store) Now I got to the point of choosing a database system and an appropriate design. I got stuck with developing a design for product attributes

I've been considering of choosing NoSQL (MongoDB) or SQL database systems

I need you advice and help

The problem:

When you choose a product type (e.g. table) it should show you the corresponding filters for such a type (e.g. height, material etc.). When you choose another type, say "car", it provides you with the car specific filter attributes (e.g. fuel, engine volume)

For example, here on one popular online store if you choose a data storage type you get a filter fo this type attributes, such as hard drive size or connection type

enter image description here

Question

What approach is the best for such a problem? I described some below, but maybe you have your own thoughts in regard to it

MongoDB

Possible solution:

You can implement such product attrs structure pretty easy.

You can create one collection with a field attrs for each product and put there whatever you want, like they suggest here (field "details"):

https://docs.mongodb.com/ecosystem/use-cases/product-catalog/#non-relational-data-model

The structure will be

enter image description here

Problem:

With such a solution you don't have product types at all so you can't filter the products out by their types. Each product contains it's own arbitrary structure in attrs field and don't follow any pattern

Ir maybe I can somehow go with this approach?

SQL

There are solutions like single table where all the products store in one table and you end up with as many fields as an attribute number of all the products taken together.

Or for every product type you create a new table

But I won't consider these ones. One is very bulky and another one isn't much flexible and requires a dynamic scheme design

Possible solution

There is one pretty flexible solution called EAV https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Our schema would be:

EAV enter image description here

Such a design may be done on MongoDB system, but I'm not sure it's been made for such a normalised structure

Problem

The schema is going to get really huge and really hard to query and grasp

mongodb database-design sql database
1 Answers

If you choose SQL database, take a look PostgreSQL which supports JSON features. Not necessarily you need to follow Database normalization.

If you choose MongoDB, you need to store attrs array with generic {key:"field", value:"value"} pairs.

{id:1, attrs:[{key: "prime", value: true}, {key:"height", value:2}, {key:"material", value:"wood"},{key:"color", "value":"brown"}]}
{id:2, attrs:[{key: "prime", value: true}, {key:"fuel", value:"gas"}, {key:"volume", "value":3}]}
{id:3, attrs:[{key: "prime", value: true}, {key:"fuel", value:"diesel"}, {key:"volume", "value":1.5}]}

Then you define Multi-key index like this:

db.collection.createIndex({"attrs.key":1, "attrs.value":1})

If you want apply step-by-step filters, use MongoDB aggregation with $elemMatch operator

☑ Prime
☑ Fuel
☐ Other

...

☑ Volume 3
☐ Volume 1.5

Query's representation

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          attrs: {
            $elemMatch: {
              key: "prime",
              value: true
            }
          }
        },
        {
          attrs: {
            $elemMatch: {
              key: "fuel"
            }
          }
        },
        {
          attrs: {
            $elemMatch: {
              key: "volume",
              "value": 3
            }
          }
        }
      ]
    }
  }
])

MongoPlayground

user_37104900
Related