close

Database Design for E-Commerce Product Variants and Prices

Here's a database design for the product section in e-commerce, with the condition that a product can have multiple variants and different prices:

Table: Products

  • ProductID (Product ID): Primary key, unique integer
  • Name (Product Name): String
  • Description: Text
  • CreatedAt: Date and time
  • UpdatedAt: Date and time

Table: Categories

  • CategoryID (Category ID): Primary key, unique integer
  • Name (Category Name): String

Table: ProductVariants

  • VariantID (Variant ID): Primary key, unique integer
  • ProductID: Foreign key referencing Products table
  • Name (Variant Name): String (e.g., Color, Size)
  • Value (Variant Value): String (e.g., Red, XL)

Table: Prices

  • PriceID (Price ID): Primary key, unique integer
  • VariantID: Foreign key referencing ProductVariants table
  • Price: Decimal
  • Currency: String (e.g., USD, VND)

Table: ProductImages

  • ImageID (Image ID): Primary key, unique integer
  • ProductID: Foreign key referencing Products table
  • ImageURL: String

Table: Reviews

  • ReviewID Primary key, unique integer
  • ProductID: Foreign key referencing Products table
  • Rating: Integer (usually from 1 to 5)
  • Comment: Text
  • CreatedAt: Date and time

In this design, the ProductVariants table contains information about different variants of a product, such as color, size. The Prices table stores price information for each product variant. Each variant can have multiple prices based on different currencies.

Please note that database design can vary based on specific project requirements and how you want to manage products and pricing.