Optimal MongoDB schema design for E-commerce products with custom pricing and variants

Ecom
1

Hey folks! I’m building an online store using Express.js and MongoDB. I’m stumped on how to structure my product collection. I need to handle custom pricing for different variants efficiently.

Here’s what I’m dealing with:

Product: Smartphone X
Variants:
- 4GB RAM, 64GB storage, Pink: $999
- 4GB RAM, 64GB storage, Gold: $1099
- 4GB RAM, 128GB storage, Gold: $1299
- 6GB RAM, 128GB storage, Gold: $1499

I tried a schema with a base price and variant add-ons, but it’s getting messy with all the combinations. Any ideas for a better approach? I’m aiming for something scalable and easy to query. Thanks for your help!

2

hey mike! ive dealt with similar setup. consider nesting variants within the product doc:

{
  name: 'smartphone x',
  variants: [
    {ram: '4gb', storage: '64gb', color: 'pink', price: 999},
    {ram: '4gb', storage: '64gb', color: 'gold', price: 1099},
    ...
  ]
}

keeps things organized & queryable. hope this helps!

3

Hey Mike_Energetic! That’s a tricky one, isn’t it? :thinking: Have you considered using a combination of embedded documents and arrays? Something like this might work:

{
  name: 'Smartphone X',
  basePrice: 999,
  variants: [
    {
      ram: '4GB',
      storage: ['64GB', '128GB'],
      colors: ['Pink', 'Gold'],
      priceAdjustments: {
        'Gold': 100,
        '128GB': 200,
        '6GB': 400
      }
    },
    {
      ram: '6GB',
      storage: ['128GB'],
      colors: ['Gold'],
      priceAdjustments: {}
    }
  ]
}

This way, you can calculate the final price by adding up the base price and relevant adjustments. It’s more flexible for future changes too. What do you think? Have you tried something similar before?

4

I’ve worked on similar e-commerce projects, and found that a flattened structure tends to be more efficient for querying and updating. Consider this approach:

{
  name: 'Smartphone X',
  variants: [
    {
      ram: '4GB',
      storage: '64GB',
      color: 'Pink',
      price: 999,
      sku: 'SMX-4-64-P'
    },
    {
      ram: '4GB',
      storage: '64GB',
      color: 'Gold',
      price: 1099,
      sku: 'SMX-4-64-G'
    },
    // Additional variants...
  ]
}

This structure allows for easy indexing on variant attributes and straightforward price updates. It also simplifies inventory management by including a unique SKU for each variant. While it may lead to some data duplication, the improved query performance often outweighs the storage cost in most e-commerce scenarios.