This document outlines a comprehensive MongoDB schema design for an e-commerce store application. The design prioritizes performance, scalability, and flexibility, leveraging MongoDB's document model to represent complex relationships naturally. It incorporates best practices such as embedding frequently accessed, one-to-few related data and referencing data for one-to-many or many-to-many relationships to optimize read operations and maintain data integrity. Key entities like Products, Users, Orders, Carts, Categories, and Reviews are defined with detailed field specifications, data types, and rationales for design choices.
The following collections form the backbone of the e-commerce store's data model.
products CollectionStores information about individual products available for sale.
average_rating improve read performance.
#### 2.3. `orders` Collection
Records placed orders by customers.
* **Rationale:** Orders are critical and need to be historically accurate. Product details (name, price) and addresses are denormalized (embedded) to preserve the state at the time of purchase, preventing issues if product details or user addresses change later. `items` array is embedded as it's typically accessed entirely with the order.
* **Document Structure:**
* Addresses & Payment Methods in users: These are usually fetched alongside user profiles and have a one-to-few relationship. Embedding avoids extra queries.
* Order Items, Shipping/Billing Addresses, Payment Info in orders: Crucial for historical accuracy. Embedding ensures the order's state at the time of purchase is preserved, even if product prices or user addresses change later. This denormalization significantly improves read performance for individual orders.
* Cart Items in carts: Carts are frequently accessed and items are always fetched together.
* product_id in reviews, carts, orders: Products are distinct entities. Referencing prevents data duplication and allows for independent updates to product details.
* user_id in orders, carts, reviews: Users are distinct. Referencing allows for independent user profile updates.
* category_id in products: Categories are separate, allowing for flexible category management and hierarchies without duplicating category data in every product.
* average_rating and number_of_reviews in products: Pre-calculating and storing these values in the products collection avoids expensive aggregation queries every time a product is viewed. These can be updated asynchronously when a new review is added.
* product_name, SKU, price_at_purchase in orders.items: Ensures historical accuracy and fast retrieval of order details without needing to join with the products collection.
* Decimal128 for Monetary Values: Crucial for financial accuracy, avoiding floating-point precision issues.
* ISODate for Timestamps: Standardized format for dates and times.
* ObjectId for IDs: MongoDB's default, efficient, and ensures uniqueness.
Effective indexing is crucial for query performance.
* users.username
* users.email
* products.slug
* products.SKU
* categories.name
* categories.slug
* carts.user_id (ensures one cart per user)
* orders.order_number
* discounts.code
* products.category_id (for filtering products by category)
* products.is_active (for filtering active products)
* products.brand (for filtering by brand)
* products.stock_quantity (for low stock alerts)
* reviews.product_id (for fetching all reviews for a product)
* reviews.user_id (for fetching all reviews by a user)
* reviews.rating (for filtering by rating)
* reviews.is_approved (for moderation queues)
* orders.user_id (for fetching all orders by a user)
* orders.order_date (for time-based order queries)
* orders.status (for filtering orders by status)
* discounts.start_date, discounts.end_date, discounts.is_active (for finding active discounts)
* discounts.applies_to, discounts.product_ids, discounts.category_ids (for applying discounts)
* products: { category_id: 1, is_active: 1, price: 1 } (for category product listings, sorted by price)
* products: { name: "text", description: "text", tags: "text" } (for full-text search, requires text index configuration)
* reviews: { product_id: 1, rating: -1 } (for reviews of a product, sorted by highest rating)
* products collection: category_id or a custom hash of _id could be a good shard key.
* users collection: _id or username/email if queries are often user-centric.
* orders collection: user_id (if most queries are for a single user's orders) or order_date (if time-based range queries are common).
stock_quantity, incrementing used_count for discounts) using $inc operator.carts.updated_at to automatically remove old documents.