Breaking News: Grepper is joining You.com. Read the official announcement!

E-commerce DB schema design

TRIDEEP SAHA answered on November 12, 2023 Popularity 7/10 Helpfulness 2/10

answer E-commerce DB schema design

E-commerce DB schema design

Comment

// User Management
Table User {
  id INT [pk, not null]
  username VARCHAR(255) [not null]
  email VARCHAR(255) [not null]
  password VARCHAR(255) [not null]
}

// Product Management
Table ProductCategory {
  id INT [pk, not null]
  name VARCHAR(255) [not null]
  description VARCHAR(255)
}

Table Product {
  id INT [pk, not null]
  name VARCHAR(255) [not null]
  description VARCHAR(255)
  inventoryId INT [not null]
  price DECIMAL(10,2) [not null]
  discountId INT [not null]
  categoryId INT [not null]
}

Table ProductInventory {
  id INT [pk, not null]
  sku VARCHAR(255) [not null]
  quantity INT [not null]
}

Table Discount {
  id INT [pk, not null]
  name VARCHAR(255) [not null]
  description VARCHAR(255)
  discountPercentage DECIMAL(10,2) [not null]
  active BOOLEAN [not null, default: true]
}

// Order Management
Table Order {
  id INT [pk, not null]
  userId INT [not null]
  createdAt TIMESTAMP [not null, default: `current_timestamp`]
  modifiedAt TIMESTAMP [not null, default: `current_timestamp`]
}

Table OrderItem {
  id INT [pk, not null]
  orderId INT [not null, fk: Order.id]
  productId INT [not null, fk: Product.id]
  quantity INT [not null]
  unitPrice DECIMAL(10,2) [not null]
}

Table OrderDetails {
  id INT [pk, not null]
  orderId INT [not null, fk: Order.id]
  userId INT [not null, fk: User.id]
  shippingAddress VARCHAR(255) [not null]
  billingAddress VARCHAR(255) [not null]
  shippingMethod VARCHAR(255) [not null]
  paymentMethod VARCHAR(255) [not null]
  totalAmount DECIMAL(10,2) [not null]
}

// Payment Management
Table UserPayment {
  id INT [pk, not null]
  userId INT [not null, fk: User.id]
  paymentType VARCHAR(255) [not null]
  provider VARCHAR(255) [not null]
  accountNo VARCHAR(255) [not null]
  expiry DATE [not null]
}

Table PaymentDetails {
  id INT [pk, not null]
  orderId INT [not null, fk: Order.id]
  paymentType VARCHAR(255) [not null]
  paymentProcessor VARCHAR(255) [not null]
  transactionId VARCHAR(255) [not null]
  amount DECIMAL(10,2) [not null]
}

// Shipment Management
Table Shipment {
  id INT [pk, not null]
  orderId INT [not null, fk: Order.id]
  trackingNumber VARCHAR(255) [not null]
  shippingCarrier VARCHAR(255) [not null]
  estimatedDeliveryDate DATE [not null]
}

// Relationships

// User to UserPayment
// User 1 ----> * UserPayment
Ref: User.id < UserPayment.userId

// Product to ProductInventory
// Product 1 ----> * ProductInventory
Ref: Product.id < ProductInventory.id

// Product to ProductCategory
// Product 1 ----> 1 ProductCategory
Ref: Product.categoryId > ProductCategory.id

// Product to Discount
// Product * ----> 1 Discount
Ref: Product.discountId > Discount.id

// Order to OrderItem
// Order 1 ----> * OrderItem
Ref: Order.id < OrderItem.orderId
// Order to OrderDetails
// Order 1 ----> 1 OrderDetails
Ref: Order.id < OrderDetails.orderId
// Order to PaymentDetails
// Order 1 ----> 1 PaymentDetails
Ref: Order.id < PaymentDetails.orderId
// Order to Shipment
// Order 1 ----> 1 Shipment
Ref: Order.id < Shipment.orderId

Popularity 7/10 Helpfulness 2/10 Language whatever

Source: chat.openai.com

Tags: e-commerce whatever

Link to this answer
Share Copy Link

Contributed on Nov 12 2023

TRIDEEP SAHA

0 Answers Avg Quality 2/10

E-commerce DB schema design

Contents

More Related Answers

E-commerce DB schema design

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.