Subramanya's Digital Garden

Recent Notes

Database Design schema for Order Management

2 min read

The schema is normalized to the 3rd Normal Form (3NF) to eliminate redundancy and ensure data integrity. Each table has a primary key, and foreign keys are used to establish relationships between tables.

Core Entities

The main entities in an order management system are:

  • Customers: Individuals or companies placing orders.
  • Products: The items that can be ordered.
  • Orders: The transaction itself, linking a customer to one or more products.
  • Payments: Details about the payment for an order.
  • Shipments: Information about the delivery of an order.

Database Schema

  1. Customers Table
    • CustomerID (Primary Key)
    • FirstName
    • LastName
    • Email
    • Phone
    • CreatedAt
    • UpdatedAt
  2. Addresses Table
    • AddressID (Primary Key)
    • CustomerID (Foreign Key to Customers)
    • AddressType
    • Street
    • City
    • State
    • ZipCode
    • Country
    • CreatedAt
    • UpdatedAt
  3. Products Table
    • ProductID (Primary Key)
    • ProductName
    • Description
    • CategoryId (Foreign Key to Categories)
    • sku
    • Price
    • StockQuantity
    • CreatedAt
    • UpdatedAt
  4. Orders Table
    • OrderID (Primary Key)
    • CustomerID (Foreign Key to Customers)
    • OrderDate
    • Status (e.g., Pending, Shipped, Delivered, Canceled))
    • TotalAmount
    • ShippingAddressID (Foreign Key to Addresses)`
    • BillingAddressID (Foreign Key to Addresses)`
    • CreatedAt
    • UpdatedAt
  5. OrderItems Table A Junction table that links Orders and Products. An order can have multiple products, and a product can be part of multiple orders.
    • OrderItemID (Primary Key)
    • OrderID (Foreign Key to Orders)
    • ProductID (Foreign Key to Products)
    • Quantity
    • UnitPrice
    • TotalPrice
    • CreatedAt
    • UpdatedAt
  6. Payments Table Stores payment transactions related to an order.
    • PaymentID (Primary Key)
    • OrderID (Foreign Key to Orders)
    • PaymentDate
    • PaymentMethod (e.g., Credit Card, PayPal, Bank Transfer)
    • Amount
    • Status (e.g., Completed, Pending, Failed)
  7. Shipments Table Tracks the shipping of items for an order. An order could potentially have multiple shipments. - ShipmentID (Primary Key) - OrderID (Foreign Key to Orders) - ShipmentDate - Carrier - TrackingNumber - Status (e.g., In Transit, Delivered, Returned)

Relationships

  • Customers to Orders: One-to-Many (Customers.customer_id → Orders.customer_id). A customer can have many orders, but an order belongs to one customer.
  • Customers to Addresses: One-to-Many (Customers.customer_id → Addresses.customer_id). A customer can have multiple addresses.
  • Orders to OrderItems: One-to-Many (Orders.order_id → OrderItems.order_id). An order can contain multiple items.
  • Products to OrderItems: One-to-Many (Products.product_id → OrderItems.product_id). A product can be part of multiple order items.
  • Orders to Payments: One-to-Many (Orders.order_id → Payments.order_id). An order can have multiple payment attempts or transactions.
  • Orders to Shipments: One-to-Many (Orders.order_id → Shipments.order_id). An order can have multiple shipments.
  • Products to Categories: Many-to-One (Products.category_id → Categories.category_id). A product belongs to one category, but a category can have multiple products.

Graph View