Database Design from Scratch: A Complete Guide

1. Requirements Analysis

Gathering Requirements

plaintextCopy1. Identify business needs 2. Define data requirements 3. Determine relationships 4. Establish constraints 5. Document requirements

Sample Business Requirements Document

sqlCopy/* Example: E-commerce System Requirements */ Business Needs: - Store customer information - Track orders and products - Manage inventory - Handle payments - Record shipping details

2. Conceptual Design

Entity Identification

plaintextCopyMain Entities: 1. Customers 2. Products 3. Orders 4. Categories 5. Payments

Entity Relationship Diagram (ERD)

sqlCopy/* Basic ERD Relationships */ Customer ---(1:N)--- Orders Orders ---(N:1)--- Products Products ---(N:1)--- Categories Orders ---(1:1)--- Payments

3. Logical Design

Table Structure

sqlCopy/* Customer Table */ CREATE TABLE Customers ( customer_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) UNIQUE, phone VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); /* Product Table */ CREATE TABLE Products ( product_id INT PRIMARY KEY, name VARCHAR(100), description TEXT, price DECIMAL(10,2), category_id INT, stock_quantity INT, FOREIGN KEY (category_id) REFERENCES Categories(category_id) );

Normalization Example

sqlCopy/* First Normal Form (1NF) */ /* Before */ CREATE TABLE Orders ( order_id INT, customer_id INT, products VARCHAR(255) -- "1,2,3,4" ); /* After */ CREATE TABLE Orders ( order_id INT, customer_id INT ); CREATE TABLE OrderItems ( order_id INT, product_id INT, quantity INT, FOREIGN KEY (order_id) REFERENCES Orders(order_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) );

4. Physical Design

Index Creation

sqlCopy/* Performance Optimization */ CREATE INDEX idx_customer_email ON Customers(email); CREATE INDEX idx_product_category ON Products(category_id); CREATE INDEX idx_order_date ON Orders(order_date);

Partitioning Strategy

sqlCopy/* Example: Range Partitioning */ CREATE TABLE Orders ( order_id INT, order_date DATE, customer_id INT ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024) );

5. Implementation

Complete Database Schema

sqlCopy/* Complete Schema Example */ 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) UNIQUE NOT NULL, phone VARCHAR(20), address TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE Categories ( category_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, description TEXT ); CREATE TABLE Products ( product_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, category_id INT, stock_quantity INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES Categories(category_id) ); 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), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ); CREATE TABLE OrderItems ( order_id INT, product_id INT, quantity INT NOT NULL, price_per_unit DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES Orders(order_id), FOREIGN KEY (product_id) REFERENCES Products(product_id), PRIMARY KEY (order_id, product_id) );

6. Data Integrity

Constraints

sqlCopy/* Data Integrity Rules */ ALTER TABLE Products ADD CONSTRAINT check_price CHECK (price > 0); ALTER TABLE OrderItems ADD CONSTRAINT check_quantity CHECK (quantity > 0); ALTER TABLE Customers ADD CONSTRAINT valid_email CHECK (email LIKE '%@%.%');

7. Security

User Permissions

sqlCopy/* Role-based Access Control */ CREATE ROLE 'app_read'; CREATE ROLE 'app_write'; GRANT SELECT ON database.* TO 'app_read'; GRANT SELECT, INSERT, UPDATE ON database.* TO 'app_write'; CREATE USER 'reader'@'localhost' IDENTIFIED BY 'password'; GRANT 'app_read' TO 'reader'@'localhost';

8. Performance Optimization

Query Optimization

sqlCopy/* Example: Optimized Queries */ -- Instead of SELECT * FROM Orders WHERE customer_id = 1; -- Use SELECT order_id, order_date, status FROM Orders WHERE customer_id = 1; -- Use JOIN with indexes SELECT o.order_id, p.name, oi.quantity FROM Orders o INNER JOIN OrderItems oi ON o.order_id = oi.order_id INNER JOIN Products p ON oi.product_id = p.product_id WHERE o.customer_id = 1;

9. Backup and Recovery

Backup Strategy

sqlCopy/* Backup Script Example */ DELIMITER // CREATE PROCEDURE BackupDatabase() BEGIN SET @backup_path = CONCAT('/backup/db_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql'); SET @backup_cmd = CONCAT('mysqldump -u root -p database > ', @backup_path); PREPARE stmt FROM @backup_cmd; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // DELIMITER ;

10. Maintenance

Maintenance Procedures

sqlCopy/* Regular Maintenance Tasks */ -- Table Optimization OPTIMIZE TABLE Orders; -- Update Statistics ANALYZE TABLE Products; -- Clean Old Data DELETE FROM Logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Best Practices

Design Guidelines

1. Use appropriate data types
2. Implement proper indexing
3. Follow naming conventions
4. Document everything
5. Plan for scalability
6. Consider security from start

Common Pitfalls to Avoid

1. Over-normalization
2. Poor indexing strategy
3. Inadequate documentation
4. Lack of backup strategy
5. Insufficient testing

Conclusion

Remember to:

• Start with thorough requirements
• Design for scalability
• Implement security early
• Optimize for performance
• Plan for maintenance
• Document everything
• Test thoroughly