基于Express+Mongoose的产品/价格数据MongoDB Schema最佳实践

Hey there! Let's dive into the best practices for your product/price MongoDB schema using Mongoose, since you're already building on Express and have a basic structure laid out. I'll break this down into actionable design tips, code examples, and performance optimizations tailored to your use case.

First, let's refine the base product fields to enforce data integrity and improve queryability:

• Enforce required fields: Brand, name, and type should be marked as required to avoid incomplete product entries.
• Use enums for controlled values: For fields like type or currency, enums prevent invalid data (e.g., typos in product categories).
• Add default values: Set enabled to true by default so new products are active unless specified otherwise.
• Precise data types for prices: Avoid using Number for currency—MongoDB's Decimal128 (supported by Mongoose) eliminates floating-point precision errors, which is critical for financial data.

Your existing price_history array is a great start, but we should structure each entry explicitly to make it useful and queryable:
Each price history entry should include:

• timestamp: Auto-set to the current time when the price change is recorded (no manual input needed).
• price: The actual price value (using Decimal128).
• currency: The currency code (e.g., USD, EUR) with a default value to ensure consistency.
• source: Where the price came from (e.g., "website", "promotion", "retail")—this helps track why a price changed.
• note (optional): A short description for context (e.g., "Black Friday sale").

• Add a currentPrice field: Instead of querying the price_history array every time you need the latest price, store it as a top-level field. This cuts down on array traversal and speeds up common queries.
• Strategic indexing:
  • A compound index on { enabled: 1, brand: 1, type: 1 } to quickly fetch active products by brand and category.
  • An index on { 'price_history.timestamp': -1 } to efficiently query price changes over time.
• Auto-maintain price history: Use a Mongoose pre-save middleware to automatically add a new entry to price_history whenever currentPrice is updated—no need to write this logic manually every time.

Here's how to put all this together in code:

const mongoose = require('mongoose');
const { Decimal128 } = mongoose.Types;

// Define the sub-schema for price history
const PriceHistorySchema = new mongoose.Schema({
  timestamp: {
    type: Date,
    default: Date.now,
    required: true
  },
  price: {
    type: Decimal128,
    required: true,
    validate: {
      validator: (val) => val >= 0,
      message: 'Price cannot be a negative value'
    }
  },
  currency: {
    type: String,
    default: 'USD',
    enum: ['USD', 'EUR', 'GBP'], // Expand this list based on your business needs
    required: true
  },
  source: {
    type: String,
    enum: ['system', 'promotion', 'retail', 'api'],
    required: true
  },
  note: {
    type: String,
    trim: true,
    maxlength: 200
  }
});

// Main product schema
const ProductSchema = new mongoose.Schema({
  enabled: {
    type: Boolean,
    default: true,
    index: true
  },
  brand: {
    type: String,
    required: [true, 'Product brand is required'],
    trim: true,
    index: true
  },
  name: {
    type: String,
    required: [true, 'Product name is required'],
    trim: true,
    index: true
  },
  type: {
    type: String,
    required: [true, 'Product type is required'],
    enum: ['electronics', 'apparel', 'home-goods', 'beauty'], // Customize your categories
    trim: true,
    index: true
  },
  currentPrice: {
    type: Decimal128,
    required: [true, 'Current product price is required'],
    validate: {
      validator: (val) => val >= 0,
      message: 'Current price cannot be negative'
    }
  },
  price_history: [PriceHistorySchema]
});

// Create indexes for faster queries
ProductSchema.index({ enabled: 1, brand: 1, type: 1 });
ProductSchema.index({ 'price_history.timestamp': -1 });

// Pre-save middleware to auto-update price history
ProductSchema.pre('save', function(next) {
  // Only add to history if currentPrice was modified
  if (this.isModified('currentPrice')) {
    this.price_history.push({
      price: this.currentPrice,
      source: 'system' // Adjust this based on how the price is being updated (e.g., 'promotion')
    });
  }
  next();
});

module.exports = mongoose.model('Product', ProductSchema);

• Archive old price history: Over time, price_history can grow large. Consider moving older entries to a separate "product_price_archives" collection to keep your main products collection lean.
• Data validation: The schema includes basic validation, but you can add more (e.g., minimum price thresholds, brand name formatting) based on your business rules.
• Avoid over-nesting: If you find yourself adding more complex data to price_history, evaluate if it makes sense to split it into a separate collection (though for price history, embedding is usually the right call due to how often you'll query prices alongside product data).
• Test query performance: Use MongoDB's explain() method to check if your indexes are being used effectively as your dataset grows.

内容的提问来源于stack exchange，提问作者Miles Collier