Database Design Best Practices

Table Names: ✓ Use lowercase with underscores (snake_case) ✓ Plural nouns: users, orders, products ✓ Descriptive and concise: order_items, not oi ✓ Avoid prefixes like tbl_ Examples: ✓ users ✓ order_items ✓ product_categories ❌ tblUser, Orders_Table, OI Column Names: ✓ Use lowercase with underscores (snake_case) ✓ Descriptive names: first_name, not fname ✓ Boolean columns: is_active, has_discount ✓ Date columns: created_at, updated_at, published_at Examples: ✓ first_name ✓ email_address ✓ is_published ✓ created_at ❌ fName, emailAddr, active, createDate Primary Key: ✓ Simply "id" for most tables ✓ INT UNSIGNED AUTO_INCREMENT ✓ Alternative: table_name_id (e.g., user_id) Foreign Key: ✓ Use the referenced table name + _id ✓ user_id, product_id, category_id ✓ Clear relationship indication

1. Timestamps Pattern Add created_at and updated_at to every table CREATE TABLE posts ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200), content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 2. Soft Deletes Pattern Use deleted_at instead of physically deleting records CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255), deleted_at TIMESTAMP NULL -- NULL means not deleted ); -- Query only active records SELECT * FROM users WHERE deleted_at IS NULL; -- Soft delete a user UPDATE users SET deleted_at = NOW() WHERE id = 1; 3. Status/State Pattern Use ENUM for well-defined states CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending' ); 4. Lookup Tables Pattern For frequently changing categories CREATE TABLE order_statuses ( id INT PRIMARY KEY, name VARCHAR(50), description TEXT ); CREATE TABLE orders ( id INT PRIMARY KEY, status_id INT, FOREIGN KEY (status_id) REFERENCES order_statuses(id) );

Hard Delete (Permanent): DELETE FROM users WHERE id = 1; Pros: ✓ Simple and straightforward ✓ Reduces database size ✓ True data removal Cons: ❌ Data is permanently lost ❌ Cannot undo mistakes ❌ Breaks referential integrity ❌ Loses historical data Soft Delete (Flag as deleted): UPDATE users SET deleted_at = NOW() WHERE id = 1; Pros: ✓ Recoverable (undo deletion) ✓ Maintains referential integrity ✓ Preserves historical data ✓ Audit trail available Cons: ❌ Increased database size ❌ Must filter deleted records in queries ❌ Adds complexity When to Use Each: - Hard Delete: Truly disposable data (logs, temporary data) - Soft Delete: Important business data (users, orders, products)

Standard Implementation: CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200), content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); Benefits: ✓ Automatic tracking of record creation time ✓ Automatic tracking of last modification time ✓ No application logic needed ✓ Useful for sorting, filtering, auditing Query Examples: -- Recently created articles SELECT * FROM articles WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY created_at DESC; -- Recently updated articles SELECT * FROM articles ORDER BY updated_at DESC LIMIT 10; -- Articles never updated SELECT * FROM articles WHERE created_at = updated_at;

Auto-increment INT (Traditional): CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) ); Pros: ✓ Simple and efficient ✓ Small storage (4 bytes) ✓ Sequential and sortable ✓ Fast index performance ✓ Human-readable Cons: ❌ Predictable (security concern) ❌ Sequential reveals business info ❌ Difficult in distributed systems ❌ Collision risk in merges UUID (Universally Unique Identifier): CREATE TABLE users ( id CHAR(36) PRIMARY KEY DEFAULT (UUID()), -- MySQL 8.0+ name VARCHAR(100) ); -- Example UUID: "550e8400-e29b-41d4-a716-446655440000" Pros: ✓ Globally unique (no collisions) ✓ Non-sequential (more secure) ✓ Great for distributed systems ✓ Can generate client-side ✓ Easy database merges Cons: ❌ Larger storage (36 bytes as CHAR) ❌ Slower indexing ❌ Not human-readable ❌ Random order (affects index fragmentation) Recommendation: - Use INT AUTO_INCREMENT for most cases - Use UUID for: distributed systems, public APIs, security-sensitive IDs - Alternative: BIGINT with Snowflake ID algorithm

Scenario: Comments can be on Posts or Videos ❌ Poor Solution (Multiple nullable FKs): CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT, post_id INT NULL, video_id INT NULL ); -- Problem: Unclear which one is used, no FK constraint ✓ Better Solution (Polymorphic Pattern): CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT, commentable_type VARCHAR(50), -- 'post' or 'video' commentable_id INT ); -- Query comments for a specific post SELECT * FROM comments WHERE commentable_type = 'post' AND commentable_id = 1; ✓ Best Solution (Separate Tables): CREATE TABLE post_comments ( id INT PRIMARY KEY, post_id INT, content TEXT, FOREIGN KEY (post_id) REFERENCES posts(id) ); CREATE TABLE video_comments ( id INT PRIMARY KEY, video_id INT, content TEXT, FOREIGN KEY (video_id) REFERENCES videos(id) ); Trade-offs: - Polymorphic: Flexible but no FK constraints - Separate tables: More tables but enforced integrity - Choose based on: similarity of data and query patterns

1. Users & Authentication CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash CHAR(60) NOT NULL, first_name VARCHAR(50), last_name VARCHAR(50), phone VARCHAR(20), is_active BOOLEAN DEFAULT TRUE, email_verified_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_email (email), INDEX idx_active (is_active, deleted_at) ); 2. Addresses (One-to-Many with Users) CREATE TABLE addresses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, address_type ENUM('billing', 'shipping', 'both') DEFAULT 'shipping', street_address VARCHAR(255) NOT NULL, city VARCHAR(100) NOT NULL, state VARCHAR(100), postal_code VARCHAR(20), country CHAR(2) DEFAULT 'US', is_default BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_user (user_id) ); 3. Product Categories CREATE TABLE categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id INT UNSIGNED NULL, -- For nested categories name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE NOT NULL, description TEXT, image_url VARCHAR(255), is_active BOOLEAN DEFAULT TRUE, display_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL, INDEX idx_parent (parent_id), INDEX idx_slug (slug) ); 4. Products CREATE TABLE products ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED, sku VARCHAR(50) UNIQUE NOT NULL, name VARCHAR(200) NOT NULL, slug VARCHAR(200) UNIQUE NOT NULL, description TEXT, short_description VARCHAR(500), price DECIMAL(10,2) NOT NULL, compare_at_price DECIMAL(10,2), -- Original price for discounts cost DECIMAL(10,2), -- Cost to seller stock_quantity INT UNSIGNED DEFAULT 0, weight_grams INT UNSIGNED, is_active BOOLEAN DEFAULT TRUE, is_featured BOOLEAN DEFAULT FALSE, image_url VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, INDEX idx_category (category_id), INDEX idx_sku (sku), INDEX idx_slug (slug), INDEX idx_featured (is_featured, is_active) ); 5. Product Images (One-to-Many) CREATE TABLE product_images ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, image_url VARCHAR(255) NOT NULL, alt_text VARCHAR(255), display_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, INDEX idx_product (product_id) ); 6. Orders CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, order_number VARCHAR(50) UNIQUE NOT NULL, -- e.g., "ORD-2024-00001" status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', subtotal DECIMAL(10,2) NOT NULL, tax DECIMAL(10,2) DEFAULT 0, shipping DECIMAL(10,2) DEFAULT 0, discount DECIMAL(10,2) DEFAULT 0, total DECIMAL(10,2) NOT NULL, currency CHAR(3) DEFAULT 'USD', shipping_address_id INT UNSIGNED, billing_address_id INT UNSIGNED, notes TEXT, ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, shipped_at TIMESTAMP NULL, delivered_at TIMESTAMP NULL, cancelled_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (shipping_address_id) REFERENCES addresses(id), FOREIGN KEY (billing_address_id) REFERENCES addresses(id), INDEX idx_user (user_id), INDEX idx_status (status), INDEX idx_ordered_at (ordered_at) ); 7. Order Items (Junction Table) CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, product_name VARCHAR(200), -- Snapshot at purchase time sku VARCHAR(50), -- Snapshot at purchase time quantity INT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL, -- Price at purchase time subtotal DECIMAL(10,2) NOT NULL, -- quantity * price created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id), INDEX idx_order (order_id), INDEX idx_product (product_id) ); 8. Shopping Cart CREATE TABLE cart_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, quantity INT UNSIGNED DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, UNIQUE KEY unique_cart_item (user_id, product_id), INDEX idx_user (user_id) ); 9. Reviews CREATE TABLE product_reviews ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5), title VARCHAR(200), comment TEXT, is_verified_purchase BOOLEAN DEFAULT FALSE, is_approved BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY unique_review (product_id, user_id), INDEX idx_product (product_id), INDEX idx_rating (rating) );

Applied Best Practices: ✓ Consistent naming (snake_case) ✓ Appropriate data types (DECIMAL for money, ENUM for status) ✓ Timestamps on every table (created_at, updated_at) ✓ Soft deletes where appropriate (users) ✓ Indexes on foreign keys and frequently queried columns ✓ Product price snapshot in order_items (historical accuracy) ✓ Address separation (reusable addresses) ✓ Unique constraints (email, SKU, order_number) ✓ Proper foreign key relationships with ON DELETE actions ✓ Default values for common scenarios ✓ Check constraints for data validation (rating 1-5) ✓ Support for nested categories (parent_id) ✓ Featured/active flags for easy filtering