Schema Design

Design Process

A systematic approach to schema design involves five key steps:

1. Identify entities: What are the main things the system tracks? For an e-commerce app: Customers, Products, Orders, Reviews.

2. Define attributes: What properties does each entity have? A customer has a name, email, and address. A product has a name, price, and category.

3. Establish relationships: How do entities relate? A customer places many orders. An order contains many products. A customer writes many reviews.

4. Choose primary keys: Each table needs a unique identifier. Use auto-incrementing integers or UUIDs.

5. Define constraints: Add NOT NULL, UNIQUE, DEFAULT, and CHECK constraints to enforce data integrity.

-- Step 1-2: Entities and attributes
-- Customers entity
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 3: Relationships defined via foreign keys
-- An order belongs to one customer
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Iterate on the design. Start simple, then add complexity as requirements evolve. Avoid over-engineering the initial schema.

Real-World Example: E-Commerce Schema

Here is a complete schema for an e-commerce application covering users, products, orders, and reviews.

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(10),
    country VARCHAR(50) DEFAULT 'US',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Product categories
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    parent_category_id INT,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT,
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items (junction table for orders and products)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Reviews table
CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    review_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    UNIQUE (product_id, customer_id)
);

This schema handles:

• One customer has many orders
• One order has many items
• One product can be in many orders
• One customer can review many products
• Categories support hierarchy via parent_category_id

Choosing Data Types

Choosing the right data type affects storage, performance, and data integrity. Use the smallest type that safely holds your data.

Integer types:

-- Use appropriate integer sizes
tinyint -- 1 byte, -128 to 127 (flags, small counts)
smallint -- 2 bytes, -32768 to 32767 (status codes, categories)
int -- 4 bytes, standard primary keys
bigint -- 8 bytes, very large datasets or IDs

String types:

varchar(100) -- variable length, use when max length varies (names, emails)
char(2) -- fixed length, use for codes (state abbreviations, country codes)
text -- large text, use for descriptions and reviews

Decimal types:

decimal(10,2) -- exact precision, use for money (up to 99999999.99)
float -- approximate, use for scientific calculations
real -- approximate, single precision

Date and time types:

date -- date only (2025-01-15)
time -- time only (14:30:00)
datetime -- date and time
timestamp -- date and time with timezone awareness

Boolean type:

boolean -- TRUE/FALSE values (is_active, is_deleted)

Best practices:

• Use VARCHAR with appropriate max length, not TEXT when possible
• Use DECIMAL for money, never FLOAT
• Use TIMESTAMP for audit columns (created_at, updated_at)
• Use ENUM for fixed sets of values (status, type)
• Avoid storing large text in main query tables; use separate tables

Naming Conventions

Consistent naming conventions make schemas readable and maintainable. Follow these standards:

Tables:

• Use plural nouns: customers, orders, products
• Use snake_case: order_items, not OrderItems
• Use descriptive names: customer_addresses, not addresses

-- Good table names
customers
order_items
product_categories
user_sessions

-- Bad table names
customer (singular)
OrderItems (camelCase)
t1 (meaningless)

Columns:

• Use snake_case: first_name, not FirstName
• Primary keys: table_name_id (customer_id, order_id)
• Foreign keys: referenced_table_id (customer_id in orders)
• Boolean flags: is_active, has_subscription, was_shipped
• Timestamps: created_at, updated_at, deleted_at

-- Good column names
first_name
order_date
total_amount
is_active
created_at

-- Bad column names
firstName
date
total
active

Constraints:

• Primary keys: pk_tablename (pk_customers)
• Foreign keys: fk_tablename_referenced (fk_orders_customers)
• Unique constraints: uk_tablename_column (uk_customers_email)

-- Named constraints
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE customers
    ADD CONSTRAINT uk_customers_email
    UNIQUE (email);

Indexes:

• Primary key indexes: automatically named after the primary key
• Index naming: idx_tablename_column (idx_orders_customer_id)

Consistent naming prevents confusion when working with large schemas and multiple team members.