mongodb - How to create products table with category and brand name in NoSql?

Question

I am new in No-sql database structure. I have a products table with brand name and category. Example Apple is a brand, and Apple has some subcategories (ex: mobile,laptop,car etc). I have to create a table with brand and subcategories relations. I have tried like below example.

I have created a table called brands

{
     "brand_id":100, 
     "name": "Apple",
     "category":[{
         "id":1,
         "name":"Mobile"
     },{
         "id":2,
         "name":"Laptop"
     }]
}

Then, I have created another table called products like below

{
    "product_id":1, 
    "name": "iPhone 11",
    "category_info":{
        "brand_id":100,
        "brand_name":"Apple",
        "category_id":1,
        "category_name":"Mobile"
    }
}

How the data will be queried

• I have to display all brand name first, after click on brand name I will display all category under this brand name. After click on category I will display all product under this brand name and under this category name.

My question is, am I in right track ? Or I should create this in one table ?

Answer 1

I got drawn to this question initially when amazon-dynamodb was still associated with the question, so my solution might not apply to mongodb, but the though process should be similar.

While you described the query as one, there are actually three kinds of queries:

1. List all Brand Names
2. List all Categories per Brand Name
3. List all items in a category for a given Brand Name

These can be addressed in a single table/collection. I'm going to use the DynamoDB Terminology here, which has two kinds of keys:

1. A Partition Key (PK) that has defines which partition/shard data lives on
2. A Sort Key (SK) that defines in which order the items for a partition key live on the shard

Combined these two make up the primary key. There are also multiple kinds of operations with varying kinds of efficiency:

1. GetItem, which requires you to specify both the PK and SK and is very fast
2. Query, which requires you to specify the PK and can optionally do some filtering/logic on the SK. It can also return multiple items and is efficient.
3. Scan, which searches through all items and is very slow.

I assume there are similar concepts in mongodb.

One idea in NoSQL designs is to use a single table/collection to store all of your items (Single-Table-Design), which we're going to use. To store different kinds of items, we can do index/key overloading.

This is how your data structure may look like, the PK and SK Attributes are mandatory:

PK                  SK                  TYPE    ATTRS
BRANDS              NAME#APPLE          BRAND   {name: apple, address:...}
BRANDS              NAME#SAMSUNG        BRAND   {name: Samsung, address:...}
BRANDS              NAME#Google         BRAND   {name: Google, address:...}
BRAND#APPLE         CATEGORY#Phones     CATG    {name: Phones, description:...}
BRAND#APPLE         CATEGORY#Computers  CATG    {name: Computers, description:...}
BRAND#GOOGLE        CATEGORY#Phones     CATG    {name: Phones, description:...}
BRAND#SAMSUNG       CATEGORY#Phones     CATG    {name: Phones, description:...}
B#APPLE#CAT#Phones  PROD#IPHONE6        PROD    {name: iPhone 6, ...}
B#APPLE#CAT#Phones  PROD#IPHONE11       PROD    {name: iPhone 11, ...}
B#APPLE#CAT#Phones  PROD#IPHONE12       PROD    {name: iPhone 12, ...}
B#GOOGLE#CAT#Phones PROD#Pixel3         PROD    {name: Pixel 3, ...}

Now you can use the operations I mentioned to efficiently run your queries.

To solve 1), you do a Query on PK=BRANDS which will return all brand items.

To solve 2), you do construct the appropriate key, so for apple you can do a Query on PK=BRAND#Apple and you'll get all the categories for apple.

To solve 3), you construct the key from the brand and category, so for apple phones you'd do a query on PK=B#APPLE#CAT#Phones and get a list of all iPhones

If you want to learn about NoSQL design patterns I highly recommend watching the re:invent talks by Rick Houlihan from AWS on YouTube.