This deliverable provides comprehensive, detailed, and professional output for the "Database Schema Designer" workflow, specifically focusing on the generation of production-ready code. We will design a schema for a simplified e-commerce platform, including SQL DDL for database creation and an example ORM representation using Python with SQLAlchemy.
This output presents a robust and scalable database schema for a typical e-commerce application. It includes Data Definition Language (DDL) scripts for creating the database objects and an example of how this schema can be represented in an Object-Relational Mapping (ORM) framework, ensuring seamless integration with application logic.
The following SQL DDL is designed for a PostgreSQL database, a widely-used and feature-rich relational database system. It defines tables for users, products, categories, orders, and order items, establishing clear relationships and constraints.
-- DDL for a simplified E-commerce Database Schema (PostgreSQL)
-- Drop tables if they exist to allow for clean re-creation (for development/testing)
-- In production, use ALTER TABLE statements for migrations.
DROP TABLE IF EXISTS "OrderItems" CASCADE;
DROP TABLE IF EXISTS "Orders" CASCADE;
DROP TABLE IF EXISTS "Products" CASCADE;
DROP TABLE IF EXISTS "Categories" CASCADE;
DROP TABLE IF EXISTS "Users" CASCADE;
-- 1. Users Table
-- Stores information about registered users of the e-commerce platform.
CREATE TABLE "Users" (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the user (UUID for distributed systems)
username VARCHAR(50) NOT NULL UNIQUE, -- Unique username for login
email VARCHAR(100) NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'), -- User's email, must be unique and valid format
password_hash VARCHAR(255) NOT NULL, -- Hashed password for security
first_name VARCHAR(50),
last_name VARCHAR(50),
address TEXT, -- User's shipping/billing address
phone_number VARCHAR(20),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Timestamp of user registration
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Timestamp of last update
);
-- Index for faster lookups by email and username
CREATE INDEX idx_users_email ON "Users" (email);
CREATE INDEX idx_users_username ON "Users" (username);
-- 2. Categories Table
-- Organizes products into different categories.
CREATE TABLE "Categories" (
category_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the category
name VARCHAR(100) NOT NULL UNIQUE, -- Category name (e.g., "Electronics", "Books")
description TEXT, -- Detailed description of the category
parent_category_id UUID, -- Self-referencing FK for hierarchical categories
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_parent_category
FOREIGN KEY (parent_category_id)
REFERENCES "Categories" (category_id)
ON DELETE SET NULL -- If a parent category is deleted, child categories become top-level
);
-- Index for faster lookups by category name
CREATE INDEX idx_categories_name ON "Categories" (name);
-- 3. Products Table
-- Stores information about individual products available for sale.
CREATE TABLE "Products" (
product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the product
name VARCHAR(255) NOT NULL, -- Product name
description TEXT, -- Detailed product description
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), -- Product price, must be non-negative
stock_quantity INT NOT NULL CHECK (stock_quantity >= 0), -- Current stock level, must be non-negative
category_id UUID NOT NULL, -- Foreign key to Categories table
image_url VARCHAR(255), -- URL for product image
sku VARCHAR(50) UNIQUE, -- Stock Keeping Unit, unique identifier for inventory
is_active BOOLEAN DEFAULT TRUE, -- Whether the product is currently active/available
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_category
FOREIGN KEY (category_id)
REFERENCES "Categories" (category_id)
ON DELETE RESTRICT -- Prevent deleting a category if products are associated
);
-- Indexes for efficient searching and filtering
CREATE INDEX idx_products_name ON "Products" (name);
CREATE INDEX idx_products_category_id ON "Products" (category_id);
CREATE INDEX idx_products_price ON "Products" (price);
CREATE INDEX idx_products_sku ON "Products" (sku);
-- 4. Orders Table
-- Records customer orders.
CREATE TABLE "Orders" (
order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the order
user_id UUID NOT NULL, -- Foreign key to Users table (who placed the order)
order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Date and time the order was placed
total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0), -- Total amount of the order
status VARCHAR(50) NOT NULL DEFAULT 'Pending' CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')), -- Current status of the order
shipping_address TEXT, -- Shipping address for the order (can be different from user's default)
billing_address TEXT, -- Billing address for the order
payment_method VARCHAR(50), -- e.g., "Credit Card", "PayPal"
payment_status VARCHAR(50) DEFAULT 'Unpaid' CHECK (payment_status IN ('Unpaid', 'Paid', 'Refunded')), -- Payment status
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES "Users" (user_id)
ON DELETE CASCADE -- If a user is deleted, their orders are also deleted
);
-- Indexes for efficient order retrieval
CREATE INDEX idx_orders_user_id ON "Orders" (user_id);
CREATE INDEX idx_orders_order_date ON "Orders" (order_date);
CREATE INDEX idx_orders_status ON "Orders" (status);
-- 5. OrderItems Table
-- Junction table to store details of products within an order (many-to-many relationship between Orders and Products).
CREATE TABLE "OrderItems" (
order_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for each item in an order
order_id UUID NOT NULL, -- Foreign key to Orders table
product_id UUID NOT NULL, -- Foreign key to Products table
quantity INT NOT NULL CHECK (quantity > 0), -- Quantity of the product in this order item
price_at_purchase NUMERIC(10, 2) NOT NULL CHECK (price_at_purchase >= 0), -- Price of the product at the time of purchase
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_order
FOREIGN KEY (order_id)
REFERENCES "Orders" (order_id)
ON DELETE CASCADE, -- If an order is deleted, its items are also deleted
CONSTRAINT fk_product
FOREIGN KEY (product_id)
REFERENCES "Products" (product_id)
ON DELETE RESTRICT, -- Prevent deleting a product if it's part of existing orders
UNIQUE (order_id, product_id) -- A product can only appear once per order
);
-- Indexes for fast access to order items
CREATE INDEX idx_orderitems_order_id ON "OrderItems" (order_id);
CREATE INDEX idx_orderitems_product_id ON "OrderItems" (product_id);
-- Trigger to update 'updated_at' column automatically
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Apply trigger to all tables that have 'updated_at'
CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON "Users"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_categories_timestamp
BEFORE UPDATE ON "Categories"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_products_timestamp
BEFORE UPDATE ON "Products"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_orders_timestamp
BEFORE UPDATE ON "Orders"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_orderitems_timestamp
BEFORE UPDATE ON "OrderItems"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
The role of a Database Schema Designer is critical in building robust, scalable, and efficient applications. This study plan is meticulously crafted to guide you through the fundamental and advanced concepts necessary to master database schema design.
Upon completing this study plan, you will be able to:
This 10-week plan is structured to provide a balanced learning experience, combining theoretical understanding with practical application.
Week 1: Fundamentals of Databases & Relational Model
Week 2: Introduction to SQL & Data Definition Language (DDL)
CREATE DATABASE, CREATE TABLE, ALTER TABLE (add/modify/drop columns, constraints), DROP TABLE, TRUNCATE TABLE. Common SQL data types (INT, VARCHAR, DATE, BOOLEAN, DECIMAL). Table-level and column-level constraints (NOT NULL, UNIQUE, DEFAULT, CHECK).Week 3: Conceptual Data Modeling (ERDs)
Week 4: Logical Data Modeling & Normalization (1NF, 2NF, 3NF)
This section elaborates on the design choices, data types, and relationships defined in the SQL DDL.
* Stores core user information.
* user_id is a UUID for global uniqueness and ease of distributed system integration.
* username and email are UNIQUE and NOT NULL for user identification and login. Email also has a CHECK constraint for basic format validation.
* password_hash stores securely hashed passwords.
* created_at and updated_at timestamps are standard for auditing.
* Organizes products into a hierarchical structure using parent_category_id (self-referencing foreign key).
* name is UNIQUE to avoid duplicate category names.
* ON DELETE SET NULL for fk_parent_category ensures that if a parent category is deleted, its child categories are not deleted but instead become top-level categories.
* Details individual products.
* price and stock_quantity are NUMERIC and INT respectively, with CHECK constraints to prevent negative values.
* category_id links products to their respective categories. ON DELETE RESTRICT prevents accidental deletion of categories that have associated products.
* sku (Stock Keeping Unit) is a common unique identifier for inventory management.
* Records customer purchase orders.
* user_id links an order to the user who placed it. ON DELETE CASCADE means if a user account is deleted, all their associated orders are also removed.
* total_amount stores the sum of all items in the order.
* status and payment_status use CHECK constraints to enforce a predefined set of valid states, improving data integrity.
* A junction table that resolves the many-to-many relationship between Orders and Products. An order can have many products, and a product can be part of many orders.
* order_id and product_id form a composite UNIQUE constraint to ensure a product appears only once per order.
* quantity and price_at_purchase capture the specific details of the product at the time it was added to the order. price_at_purchase is crucial as product prices can change over time.
user_id, product_id, etc.) for better scalability in distributed environments, avoiding sequential IDs that can lead to contention, and enhancing security by making IDs harder to guess. gen_random_uuid() is a PostgreSQL function for this.username, name).description, address).price, total_amount). P is precision (total digits), S is scale (digits after decimal point).stock_quantity, quantity).is_active).CURRENT_TIMESTAMP provides the current time. * ON DELETE CASCADE: Deletes child records when parent record is deleted.
* ON DELETE RESTRICT: Prevents deletion of parent record if child records exist.
* ON DELETE SET NULL: Sets foreign key to NULL if parent record is deleted.
price >= 0, status IN (...)).Indexes are created on frequently queried columns to improve read performance:
category_id, user_id, order_id, product_id) are indexed to speed up join operations.WHERE clauses or ORDER BY clauses (e.g., email, username, name, order_date, status, price, sku) are also indexed.updated_at TriggerA PostgreSQL trigger update_timestamp is implemented to automatically update the updated_at column to the current timestamp whenever a row is modified in the Users, Categories, Products, Orders, or
As a professional AI assistant within PantheraHive, I am pleased to present the comprehensive and detailed output for the "Database Schema Designer" workflow, specifically for the review_and_document step. This document serves as the final deliverable, outlining the proposed database schema design, its rationale, and key considerations.
Project: Customer Order Management System
Deliverable: Database Schema Design Documentation
Version: 1.0
Date: October 26, 2023
This document details the proposed relational database schema design for the Customer Order Management System (COMS). The schema is engineered to support core functionalities including customer management, product catalog, order processing, and inventory tracking. Designed with a focus on data integrity, performance, scalability, and maintainability, this schema provides a robust foundation for the application. It adheres to best practices in relational database design, employing appropriate normalization levels and indexing strategies to ensure efficient data storage and retrieval.
The primary purpose of this document is to provide a complete and detailed specification of the database schema for the Customer Order Management System. It serves as a definitive guide for developers, database administrators, and stakeholders, ensuring a shared understanding of the data model that underpins the application. This design aims to meet current business requirements while providing flexibility for future growth and enhancements.
This schema design covers the following key business domains:
The database schema has been developed adhering to the following core principles:
The COMS database schema is a relational model consisting of several interconnected entities. The core entities include:
Users: Internal system users (e.g., administrators, order processors).Customers: External individuals or organizations placing orders.Addresses: Generic address entity linked to both Customers (billing/shipping defaults) and Orders (specific shipping address for an order).Categories: Hierarchical organization for Products.Products: Items available for purchase, linked to Categories and having inventory.Orders: Represents a single customer transaction, linking to Customers and a specific shipping Address.OrderItems: Details of products within an Order, including quantity and price at time of order.Payments: Records payment transactions associated with Orders.This schema design is agnostic to a specific relational database management system (RDBMS) but is highly compatible with popular choices such as PostgreSQL, MySQL, SQL Server, or Oracle. Data types are specified in a generic manner, with common equivalents available across these platforms.
This section provides a detailed breakdown of each table, including its purpose, columns, data types, constraints, and relationships.
users Table * user_id (PK, INT): Unique identifier for the user.
* username (VARCHAR(50), UNIQUE, NOT NULL): User's login username.
* password_hash (VARCHAR(255), NOT NULL): Hashed password for security.
* email (VARCHAR(100), UNIQUE, NOT NULL): User's email address.
* first_name (VARCHAR(50)): User's first name.
* last_name (VARCHAR(50)): User's last name.
* role (VARCHAR(20), DEFAULT 'staff', NOT NULL): User's role (e.g., 'admin', 'staff', 'viewer').
* is_active (BOOLEAN, DEFAULT TRUE, NOT NULL): Indicates if the user account is active.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Timestamp of account creation.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL): Last update timestamp.
customers Table * customer_id (PK, INT): Unique identifier for the customer.
* first_name (VARCHAR(50), NOT NULL): Customer's first name.
* last_name (VARCHAR(50), NOT NULL): Customer's last name.
* email (VARCHAR(100), UNIQUE, NOT NULL): Customer's email address (used for login/communication).
* phone_number (VARCHAR(20)): Customer's phone number.
* password_hash (VARCHAR(255), NOT NULL): Hashed password for customer login.
* default_shipping_address_id (FK, INT, REFERENCES addresses): Default shipping address.
* default_billing_address_id (FK, INT, REFERENCES addresses): Default billing address.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Timestamp of customer registration.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL): Last update timestamp.
addresses Table * address_id (PK, INT): Unique identifier for the address.
* customer_id (FK, INT, REFERENCES customers): Optional, links address to a customer (if it's a customer's stored address).
* address_line1 (VARCHAR(100), NOT NULL): First line of the address.
* address_line2 (VARCHAR(100)): Second line of the address (optional).
* city (VARCHAR(50), NOT NULL): City.
* state_province (VARCHAR(50), NOT NULL): State or Province.
* postal_code (VARCHAR(20), NOT NULL): Postal or Zip code.
* country (VARCHAR(50), NOT NULL): Country.
* address_type (VARCHAR(20)): E.g., 'shipping', 'billing', 'home', 'work'.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Timestamp of address creation.
categories Table * category_id (PK, INT): Unique identifier for the category.
* name (VARCHAR(100), UNIQUE, NOT NULL): Name of the category.
* description (TEXT): Description of the category.
* parent_category_id (FK, INT, REFERENCES categories): Self-referencing foreign key for hierarchical categories.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Timestamp of category creation.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL): Last update timestamp.
products Table * product_id (PK, INT): Unique identifier for the product.
* category_id (FK, INT, REFERENCES categories, NOT NULL): Category the product belongs to.
* name (VARCHAR(255), UNIQUE, NOT NULL): Product name.
* description (TEXT): Detailed product description.
* sku (VARCHAR(50), UNIQUE, NOT NULL): Stock Keeping Unit, unique product code.
* price (DECIMAL(10, 2), NOT NULL): Current selling price of the product.
* stock_quantity (INT, DEFAULT 0, NOT NULL): Current available stock quantity.
* weight (DECIMAL(10, 2)): Product weight (for shipping calculation).
* image_url (VARCHAR(255)): URL to product image.
* is_active (BOOLEAN, DEFAULT TRUE, NOT NULL): Indicates if the product is available for sale.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Timestamp of product creation.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL): Last update timestamp.
orders Table * order_id (PK, INT): Unique identifier for the order.
* customer_id (FK, INT, REFERENCES customers, NOT NULL): Customer who placed the order.
* order_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Date and time the order was placed.
* total_amount (DECIMAL(10, 2), NOT NULL): Total amount of the order, including shipping and taxes.
* order_status (VARCHAR(50), DEFAULT 'pending', NOT NULL): Current status of the order (e.g., 'pending', 'processing', 'shipped', 'delivered', 'cancelled').
* shipping_address_id (FK, INT, REFERENCES addresses, NOT NULL): The specific shipping address for this order.
* billing_address_id (FK, INT, REFERENCES addresses, NOT NULL): The specific billing address for this order.
* shipping_cost (DECIMAL(10, 2), DEFAULT 0.00, NOT NULL): Cost of shipping for this order.
* tax_amount (DECIMAL(10, 2), DEFAULT 0.00, NOT NULL): Total tax amount for this order.
* payment_status (VARCHAR(50), DEFAULT 'pending', NOT NULL): Status of payment for the order (e.g., 'pending', 'paid', 'refunded', 'failed').
* notes (TEXT): Any special notes or instructions for the order.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL): Last update timestamp.
order_items Table * order_item_id (PK, INT): Unique identifier for the order item.
* order_id (FK, INT, REFERENCES orders, NOT NULL): The order this item belongs to.
* product_id (FK, INT, REFERENCES products, NOT NULL): The product included in the order.
* quantity (INT, NOT NULL): Quantity of the product ordered.
* unit_price (DECIMAL(10, 2), NOT NULL): Price of the product at the time of order (historical price).
subtotal (DECIMAL(10, 2), NOT NULL): quantity unit_price.
* Composite UNIQUE Constraint: (order_id, product_id) to prevent duplicate products in an order.
payments Table * payment_id (PK, INT): Unique identifier for the payment.
* order_id (FK, INT, REFERENCES orders, NOT NULL): The order this payment is for.
* payment_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, NOT NULL): Date and time of the payment.
* amount (DECIMAL(10, 2), NOT NULL): Amount of the payment.
* payment_method (VARCHAR(50), NOT NULL): E.g., 'credit_card', 'paypal', 'bank_transfer'.
* transaction_id (VARCHAR(255), UNIQUE, NOT NULL): Unique transaction ID from the payment gateway.
* payment_status (VARCHAR(50), NOT NULL): Status of the payment (e.g.,