Database Design for E-Commerce Orders with Product Attributes and Multiple Price

Here's a database design for the order section in e-commerce, with products having multiple attributes and multiple prices:

Table: Users

• UserID: Primary key, unique integer
• Username: String
• Email: String
• Password: String
• CreatedAt: Date and time
• UpdatedAt: Date and time

Table: Orders

• OrderID: Primary key, unique integer
• UserID: Foreign key referencing Users table
• TotalAmount: Decimal
• OrderDate: Date

Table: OrderItems

• OrderItemID: Primary key, unique integer
• OrderID: Foreign key referencing Orders table
• ProductID: Foreign key referencing Products table
• VariantID: Foreign key referencing ProductVariants table
• Quantity: Integer
• Price: Decimal
• Subtotal: Decimal

Table: Products

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

Table: ProductVariants

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

Table: VariantPrices

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

In this design, the OrderItems table contains information about each item in an order, including details about the product, product variant, quantity, price, and subtotal.