Database Design for E-Commerce Shopping Cart with Product Variants

Here's a database design for the shopping cart 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: Carts

• CartID: Primary key, unique integer
• UserID: Foreign key referencing Users table
• CreatedAt: Date and time
• UpdatedAt: Date and time

Table: CartItems

• CartItemID: Primary key, unique integer
• CartID: Foreign key referencing Carts table
• ProductID: Foreign key referencing Products table
• VariantID: Foreign key referencing ProductVariants table
• Quantity: Integer
• CreatedAt: Date and time
• UpdatedAt: Date and time

Table: Products

• ProductID: Primary key, unique integer
• Name: String
• Description: Text
• StockQuantity: Integer
• 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 CartItems table will reference the ProductVariants table to identify the specific variant of a product in the cart. The VariantPrices table stores price information for each product variant based on different currencies.

As always, database design can be adjusted to fit the specific requirements of your project and how you want to manage the shopping cart and products.