As a professional AI assistant within PantheraHive, I am pleased to present the detailed study plan for the "Database Schema Designer" workflow. This plan is meticulously crafted to equip you with the essential knowledge and practical skills required to design robust, efficient, and scalable database schemas.
This study plan is designed to provide a structured, in-depth learning path over 8 weeks, covering fundamental to advanced concepts in database schema design. By the end of this program, you will possess the expertise to make informed decisions about database architectures, ensuring optimal performance, scalability, and maintainability for various applications.
The role of a Database Schema Designer is critical in modern software development. A well-designed schema is the backbone of any application, directly impacting its performance, scalability, and ease of maintenance. This program focuses on both relational (SQL) and non-relational (NoSQL) database design principles, data modeling techniques, performance optimization, and real-world application scenarios.
Upon successful completion of this study plan, you will be able to:
This 8-week schedule provides a detailed breakdown of topics, activities, and suggested daily focus areas. Each week requires approximately 10-15 hours of dedicated study, including reading, video lectures, hands-on practice, and project work.
Week 1: Fundamentals of Databases & Data Modeling Introduction
* Introduction to Databases: Data vs. Information, Database Management Systems (DBMS), types of DBMS (RDBMS, NoSQL, OLTP vs. OLAP).
* Relational Model Overview: Tables, Rows, Columns, Primary Keys, Foreign Keys.
* Introduction to Data Modeling: Purpose, benefits, conceptual vs. logical vs. physical models.
* Entity-Relationship Diagrams (ERDs): Entities, Attributes (simple, composite, multi-valued), Relationships (degree, cardinality: 1:1, 1:N, N:M), Weak Entities.
* Mon-Tue: Read about database fundamentals, RDBMS overview.
* Wed-Thu: Study ERD components, practice drawing simple ERDs for scenarios like a "Student-Course" system.
* Fri-Sat: Review concepts, attempt practice problems to identify entities, attributes, and relationships from textual descriptions.
Week 2: Relational Database Concepts & Normalization
* Relational Algebra and Calculus (brief overview).
* Keys in RDBMS: Candidate Key, Super Key, Alternate Key, Composite Key.
* Normalization Forms: 1NF, 2NF, 3NF, BCNF – detailed explanation and examples.
* Functional Dependencies: Understanding transitive and partial dependencies.
* Denormalization: When and why to denormalize for performance.
* Mon-Wed: Deep dive into 1NF, 2NF, 3NF with examples. Practice identifying functional dependencies and normalizing tables.
* Thu-Fri: Study BCNF and comparison with 3NF. Understand denormalization strategies.
* Sat: Practice normalizing various table structures up to BCNF.
Week 3: Advanced Relational Modeling & SQL Fundamentals
* Advanced ERD: Supertype/Subtype relationships (Generalization/Specialization), Arcs, Recursive Relationships.
* Introduction to SQL: DDL (CREATE, ALTER, DROP), DML (INSERT, SELECT, UPDATE, DELETE).
* Basic SQL Queries: Filtering (WHERE), Sorting (ORDER BY), Aggregations (COUNT, SUM, AVG, MIN, MAX).
* Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
* Mon-Tue: Explore advanced ERD concepts, practice modeling inheritance and recursive structures.
* Wed-Fri: Hands-on SQL practice: Set up a local RDBMS (e.g., PostgreSQL or MySQL). Create tables, insert data, and practice basic DDL/DML queries. Focus heavily on various types of joins.
* Sat: Work through SQL exercises involving multiple tables and different join types.
Week 4: Database Design Principles, Performance & First Project
* Schema Design Best Practices: Naming conventions, data types, constraints (NOT NULL, UNIQUE, CHECK).
* Indexing: B-tree, Hash indexes, Clustered vs. Non-clustered, when and what to index.
* Views, Stored Procedures, Functions, Triggers: Purpose and implementation.
* Query Optimization Basics: EXPLAIN plans, understanding query bottlenecks.
* Database Security: User roles, permissions.
* Mon-Wed: Learn about indexes and query optimization. Practice creating views, stored procedures, and basic triggers.
* Thu-Fri: Focus on Milestone 1 Project.
* Sat: Review all RDBMS concepts.
Milestone 1: Relational Schema Design Project (End of Week 4)
* Conceptual ERD.
* Logical ERD with attributes, primary keys, foreign keys, and relationships clearly defined.
* Physical schema (DDL scripts) including table definitions, data types, constraints, and proposed indexes.
* A brief justification for normalization choices.
Week 5: Introduction to NoSQL Databases
* CAP Theorem: Consistency, Availability, Partition Tolerance.
* Types of NoSQL Databases:
* Document-Oriented (e.g., MongoDB): Structure, use cases.
* Key-Value Stores (e.g., Redis, DynamoDB): Structure, use cases.
* Column-Family Stores (e.g., Cassandra, HBase): Structure, use cases.
* Graph Databases (e.g., Neo4j): Structure, use cases.
* Comparison: RDBMS vs. NoSQL – strengths and weaknesses.
Mon-Wed: Read extensively about the CAP theorem and the four main types of NoSQL databases. Focus on understanding when* to use each type.
* Thu-Fri: Choose one NoSQL database (e.g., MongoDB for Document-Oriented) and set it up locally. Explore its basic data model and operations.
* Sat: Research real-world examples of applications using different NoSQL databases.
Week 6: Hands-on NoSQL & Polyglot Persistence
* Data Modeling in NoSQL:
* Document DBs: Embedding vs. Referencing, denormalization strategies for performance.
* Key-Value Stores: Simple key-value pairs, use cases (caching, sessions).
* Graph DBs: Nodes, relationships, properties.
* CRUD Operations in chosen NoSQL DB (e.g., MongoDB shell/driver).
* Polyglot Persistence: Combining different database types in a single application architecture.
* Data Migration: Basic strategies for moving data between different database types.
* Mon-Wed: Practice designing schemas for a Document DB (e.g., for a social media profile or product catalog). Implement CRUD operations using the chosen NoSQL DB.
* Thu-Fri: Explore a second NoSQL type (e.g., Redis for Key-Value). Understand its specific use cases and basic commands
This document provides a comprehensive and professional database schema (Data Definition Language - DDL) for an e-commerce platform. This schema is designed to be robust, scalable, and efficient, covering core functionalities such as user management, product catalog, order processing, and reviews.
This output represents the completion of step 2 of 3 in the "Database Schema Designer" workflow, focusing on the generate_code phase. Based on common e-commerce requirements and best practices, we have designed a relational database schema. The generated DDL code is production-ready, well-commented, and optimized for clarity and maintainability.
The chosen domain for this schema is a typical e-commerce platform. This design accounts for various key entities and their relationships, enabling functionalities such as:
For this schema, we have chosen PostgreSQL as the target database system. PostgreSQL is an advanced, open-source relational database known for its:
The DDL provided below is specifically tailored for PostgreSQL syntax.
The following DDL code defines the tables, columns, data types, primary keys, foreign keys, unique constraints, and default values for the e-commerce platform. Each table definition includes comments to explain its purpose and the rationale behind specific design choices.
-- Disable foreign key checks temporarily if needed during initial setup
-- SET session_replication_role = 'replica';
-- TRUNCATE TABLE users, addresses, categories, products, product_images, carts, cart_items, orders, order_items, payments, reviews CASCADE;
-- SET session_replication_role = 'origin';
-- Drop tables in reverse order of dependency to avoid foreign key conflicts
DROP TABLE IF EXISTS reviews CASCADE;
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS cart_items CASCADE;
DROP TABLE IF EXISTS carts CASCADE;
DROP TABLE IF EXISTS product_images CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS addresses CASCADE;
DROP TABLE IF EXISTS users CASCADE;
-- CreateEnum for user roles (more robust than VARCHAR for predefined options)
CREATE TYPE user_role AS ENUM ('customer', 'admin', 'seller');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');
-- Table: users
-- Stores user account information, including customer and admin details.
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the user
username VARCHAR(50) UNIQUE NOT NULL, -- Unique username for login
email VARCHAR(100) UNIQUE NOT NULL, -- Unique email address
password_hash VARCHAR(255) NOT NULL, -- Hashed password for security
first_name VARCHAR(50),
last_name VARCHAR(50),
role user_role DEFAULT 'customer' NOT NULL, -- User role (customer, admin, seller)
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Timestamp of account creation
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Timestamp of last update
);
-- Index on email for faster lookups during login
CREATE INDEX idx_users_email ON users (email);
-- Table: addresses
-- Stores physical addresses associated with users (for shipping/billing).
CREATE TABLE addresses (
address_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the address
user_id UUID NOT NULL, -- Foreign key to users table
address_line1 VARCHAR(100) NOT NULL,
address_line2 VARCHAR(100),
city VARCHAR(50) NOT NULL,
state_province VARCHAR(50) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country VARCHAR(50) NOT NULL,
is_default_shipping BOOLEAN DEFAULT FALSE, -- Flag for default shipping address
is_default_billing BOOLEAN DEFAULT FALSE, -- Flag for default billing address
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE -- If a user is deleted, their addresses are also deleted
);
-- Index on user_id for faster address retrieval per user
CREATE INDEX idx_addresses_user_id ON addresses (user_id);
-- Table: categories
-- Organizes products into hierarchical categories.
CREATE TABLE categories (
category_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the category
name VARCHAR(100) UNIQUE NOT NULL, -- Name of the category
description TEXT, -- Detailed description of the category
parent_category_id UUID, -- Self-referencing foreign key for hierarchical categories
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL -- If a parent category is deleted, child categories become root
);
-- Table: products
-- 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, -- Name of the product
description TEXT, -- Detailed description of the product
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), -- Price of the product (2 decimal places, non-negative)
stock_quantity INT NOT NULL CHECK (stock_quantity >= 0), -- Current stock level (non-negative)
category_id UUID, -- Foreign key to categories table
seller_id UUID NOT NULL, -- Foreign key to users table (the seller)
sku VARCHAR(100) UNIQUE, -- Stock Keeping Unit (optional, unique)
is_available BOOLEAN DEFAULT TRUE, -- Flag if product is currently available for sale
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL, -- Products remain if category is deleted, but category_id becomes NULL
FOREIGN KEY (seller_id) REFERENCES users(user_id) ON DELETE RESTRICT -- Prevent deleting a user who is a seller with active products
);
-- Indexes for common lookups
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_products_seller_id ON products (seller_id);
CREATE INDEX idx_products_name ON products (name);
-- Table: product_images
-- Stores URLs or paths to images for each product.
CREATE TABLE product_images (
image_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the image
product_id UUID NOT NULL, -- Foreign key to products table
image_url VARCHAR(255) NOT NULL, -- URL or path to the image
alt_text VARCHAR(255), -- Alternative text for accessibility
is_thumbnail BOOLEAN DEFAULT FALSE, -- Flag for the main thumbnail image
sort_order INT DEFAULT 0, -- Order of images for display
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE -- If a product is deleted, its images are also deleted
);
-- Index on product_id for faster image retrieval
CREATE INDEX idx_product_images_product_id ON product_images (product_id);
-- Table: carts
-- Represents a user's shopping cart.
CREATE TABLE carts (
cart_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the cart
user_id UUID UNIQUE NOT NULL, -- Foreign key to users table (one cart per user)
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE -- If a user is deleted, their cart is also deleted
);
-- Table: cart_items
-- Stores individual items within a user's shopping cart.
CREATE TABLE cart_items (
cart_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the cart item
cart_id UUID NOT NULL, -- Foreign key to carts table
product_id UUID NOT NULL, -- Foreign key to products table
quantity INT NOT NULL CHECK (quantity > 0), -- Quantity of the product in the cart
added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (cart_id) REFERENCES carts(cart_id) ON DELETE CASCADE, -- If a cart is deleted, its items are also deleted
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE, -- If a product is deleted, remove it from carts
UNIQUE (cart_id, product_id) -- Ensure a product appears only once in a specific cart
);
-- Indexes for efficient lookups
CREATE INDEX idx_cart_items_cart_id ON cart_items (cart_id);
CREATE INDEX idx_cart_items_product_id ON cart_items (product_id);
-- Table: orders
-- Stores information about 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 (the customer)
order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
status order_status DEFAULT 'pending' NOT NULL, -- Current status of the order
total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0), -- Total amount of the order
shipping_address_id UUID NOT NULL, -- Foreign key to addresses table for shipping
billing_address_id UUID NOT NULL, -- Foreign key to addresses table for billing
-- Optional: payment_id UUID, -- Link to payment transaction (can be added later if 1:1)
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT, -- Prevent deleting a user with active orders
FOREIGN KEY (shipping_address_id) REFERENCES addresses(address_id) ON DELETE RESTRICT, -- Prevent deleting an address used in an order
FOREIGN KEY (billing_address_id) REFERENCES addresses(address_id) ON DELETE RESTRICT
);
-- Indexes for efficient lookups
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
-- Table: order_items
-- Stores individual products within an order.
CREATE TABLE order_items (
order_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the order item
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 the order
price_at_purchase NUMERIC(10, 2) NOT NULL CHECK (price_at_purchase >= 0), -- Price of the product at the time of purchase
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE, -- If an order is deleted, its items are also deleted
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT, -- Prevent deleting a product that is part of an order
UNIQUE (order_id, product_id) -- Ensure a product appears only once in a specific order
);
-- Indexes for efficient lookups
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Table: payments
-- Records payment transactions for orders.
CREATE TABLE payments (
payment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the payment
order_id UUID UNIQUE NOT NULL, -- Foreign key to orders table (one payment per order)
payment_method VARCHAR(50) NOT NULL, -- e.g., 'Credit Card', 'PayPal', 'Stripe'
amount NUMERIC(10, 2) NOT NULL CHECK (amount >= 0), -- Amount of the payment
currency VARCHAR(3) DEFAULT 'USD' NOT NULL, -- Currency of the payment (e.g., USD, EUR)
transaction_id VARCHAR(255) UNIQUE, -- Unique ID from payment gateway
status payment_status DEFAULT 'pending' NOT NULL, -- Status of the payment
payment_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE RESTRICT -- Prevent deleting an order that has a payment
);
-- Index on transaction_id for quick lookup of payment gateway status
CREATE INDEX idx_payments_transaction_id ON payments (transaction_id);
CREATE INDEX idx_payments_order_id ON payments (order_id);
-- Table: reviews
-- Stores customer reviews and ratings for products.
CREATE TABLE reviews (
review_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the review
product_id UUID NOT NULL, -- Foreign key to products table
user_id UUID NOT NULL, -- Foreign key to users table (the reviewer)
rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5), -- Rating from 1 to 5 stars
comment TEXT, -- Detailed review text
review_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE, -- If a product is deleted, its reviews are also deleted
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, -- If a user is deleted, their reviews are also deleted
UNIQUE (product_
As the final step in the "Database Schema Designer" workflow, this document provides a comprehensive and professional review of the proposed database schema, along with detailed documentation, design justifications, and actionable guidance for implementation.
This document outlines the proposed database schema, meticulously designed to meet the specified requirements for a robust, scalable, and maintainable data storage solution. The schema prioritizes data integrity, efficient querying, and extensibility, ensuring it can support current operational needs while accommodating future growth. It includes detailed table definitions, relationships, indexing strategies, and an overview of design principles.
The core of the database schema is built around a set of interconnected entities, designed to represent the key business concepts. For this deliverable, we will use a common e-commerce model as an illustrative example to demonstrate the structure.
The database schema is structured around the following primary entities and their relationships:
Relationships Summary:
Customer can have multiple Addresses.Address can be used for multiple Orders.Product belongs to one Category.Customer can place multiple Orders.Order contains multiple Order_Items.Order_Item links to one Product.Customer can write multiple Reviews for Products.Product can have multiple Reviews.Each table is defined with its purpose, columns, data types, constraints (Primary Keys, Foreign Keys, Unique Constraints), and default values.
Table: Customers
* customer_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the customer.
* first_name (VARCHAR(100), NOT NULL): Customer's first name.
* last_name (VARCHAR(100), NOT NULL): Customer's last name.
* email (VARCHAR(255), UNIQUE, NOT NULL): Customer's email address, used for login.
* password_hash (VARCHAR(255), NOT NULL): Hashed password for security.
* phone_number (VARCHAR(20), NULL): Customer's contact phone number.
* registration_date (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP): Date and time of registration.
* last_login (DATETIME, NULL): Last login date and time.
* PRIMARY KEY (customer_id)
* UNIQUE (email)
Table: Addresses
* address_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the address.
* customer_id (INT, FK to Customers.customer_id, NOT NULL): The customer associated with this address.
* street (VARCHAR(255), NOT NULL): Street address.
* city (VARCHAR(100), NOT NULL): City.
* state_province (VARCHAR(100), NOT NULL): State or Province.
* postal_code (VARCHAR(20), NOT NULL): Postal code.
* country (VARCHAR(100), NOT NULL): Country.
* is_shipping (BOOLEAN, NOT NULL, DEFAULT TRUE): Indicates if this address can be used for shipping.
* is_billing (BOOLEAN, NOT NULL, DEFAULT FALSE): Indicates if this address can be used for billing.
* PRIMARY KEY (address_id)
* FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE
Table: Categories
* category_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the category.
* category_name (VARCHAR(100), UNIQUE, NOT NULL): Name of the category (e.g., "Electronics", "Clothing").
* parent_category_id (INT, FK to Categories.category_id, NULL): For hierarchical categories (self-referencing).
* description (TEXT, NULL): Description of the category.
* PRIMARY KEY (category_id)
* UNIQUE (category_name)
* FOREIGN KEY (parent_category_id) REFERENCES Categories(category_id) ON DELETE SET NULL ON UPDATE CASCADE
Table: Products
* product_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the product.
* name (VARCHAR(255), NOT NULL): Product name.
* description (TEXT, NULL): Detailed product description.
* price (DECIMAL(10, 2), NOT NULL): Current price of the product.
* stock_quantity (INT, NOT NULL, DEFAULT 0): Current quantity in stock.
* category_id (INT, FK to Categories.category_id, NOT NULL): The category the product belongs to.
* sku (VARCHAR(50), UNIQUE, NOT NULL): Stock Keeping Unit, unique product code.
* image_url (VARCHAR(255), NULL): URL to the main product image.
* created_at (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP): Timestamp when the product was added.
* updated_at (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Last update timestamp.
* PRIMARY KEY (product_id)
* UNIQUE (sku)
* FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE RESTRICT ON UPDATE CASCADE
* INDEX (name)
Table: Orders
* order_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the order.
* customer_id (INT, FK to Customers.customer_id, NOT NULL): The customer who placed the order.
* order_date (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP): Date and time the order was placed.
* total_amount (DECIMAL(10, 2), NOT NULL): Total cost of the order.
* status (VARCHAR(50), NOT NULL, DEFAULT 'Pending'): Current status of the order (e.g., 'Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled').
* shipping_address_id (INT, FK to Addresses.address_id, NULL): The address used for shipping this order.
* billing_address_id (INT, FK to Addresses.address_id, NULL): The address used for billing this order.
* PRIMARY KEY (order_id)
* FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
* FOREIGN KEY (shipping_address_id) REFERENCES Addresses(address_id) ON DELETE SET NULL ON UPDATE CASCADE
* FOREIGN KEY (billing_address_id) REFERENCES Addresses(address_id) ON DELETE SET NULL ON UPDATE CASCADE
* INDEX (order_date)
* INDEX (status)
Table: Order_Items
Orders and Products. * order_item_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the order item.
* order_id (INT, FK to Orders.order_id, NOT NULL): The order this item belongs to.
* product_id (INT, FK to Products.product_id, NOT NULL): The product ordered.
* quantity (INT, NOT NULL, DEFAULT 1): Quantity of the product ordered.
* price_at_purchase (DECIMAL(10, 2), NOT NULL): Price of the product at the time of purchase (for historical accuracy).
* PRIMARY KEY (order_item_id)
* UNIQUE (order_id, product_id): Ensures a product is listed only once per order.
* FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE
* FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE RESTRICT ON UPDATE CASCADE
Table: Reviews
* review_id (INT, PK, NOT NULL, AUTO_INCREMENT): Unique identifier for the review.
* product_id (INT, FK to Products.product_id, NOT NULL): The product being reviewed.
* customer_id (INT, FK to Customers.customer_id, NOT NULL): The customer who wrote the review.
* rating (TINYINT, NOT NULL, CHECK (rating >= 1 AND rating <= 5)): Rating from 1 to 5 stars.
* comment (TEXT, NULL): Customer's written review.
* review_date (DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP): Date and time the review was posted.
* PRIMARY KEY (review_id)
* UNIQUE (product_id, customer_id): Ensures a customer can review a product only once.
* FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
* FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE
* INDEX (rating)
* INDEX (review_date)
The schema design incorporates several key principles and decisions to ensure optimal performance, data integrity, and flexibility:
Addresses are separated from Customers to allow multiple addresses per customer and reuse for shipping/billing. Order_Items are separated from Orders to handle multiple products per order efficiently.ON DELETE and ON UPDATE actions are carefully chosen: * CASCADE: For dependent data that should be removed or updated with the parent (e.g., deleting a customer removes their addresses and reviews).
* RESTRICT: To prevent deletion of parent records if child records exist (e.g., cannot delete a product if it's part of an order).
* SET NULL: For optional relationships where the child can exist without the parent (e.g., if a parent category is deleted, child categories become top-level).
INT for IDs, VARCHAR with reasonable lengths for strings, DECIMAL for monetary values to avoid floating-point inaccuracies, BOOLEAN for true/false flags).email for Customers, sku for Products, order_date for Orders) are added to speed up common lookup and sorting operations. Unique indexes enforce uniqueness constraints beyond primary keys.created_at and updated_at timestamps are included in key tables (Products, Customers) to provide valuable auditing information for tracking changes over time.password_hash to emphasize that raw passwords should never be stored directly.This section provides actionable steps and considerations for implementing the designed database schema.
Below are example DDL (Data Definition Language) scripts for creating the tables and constraints. These scripts are compatible with most SQL-compliant relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, or Oracle, with minor syntax adjustments if necessary.
-- Create