Database Architecture Capstone | Design Complete Schemas

# CHAPTER 20

Final Project: Design Complete Production Databases #

1. Introduction #

2. Project Requirements #

Your architecture must include:

1. 1. A perfectly normalized schema (3NF).

1. 2. Appropriate Primary Keys and Foreign Keys enforcing Referential Integrity.

1. 3. Resolutions for Many-to-Many relationships using Junction Tables.

1. 4. Optimized Data Types for performance.

1. 5. Security constraints (NOT NULL, UNIQUE).

1. 6. A Denormalization strategy for performance.

3. Phase 1: Requirement Analysis & Business Rules (Chapter 4) #

• Users can be either 'Students' or 'Instructors'.

• An Instructor can create many Courses. A Course belongs to exactly one Instructor.

• A Course contains many Lessons.

• A Student can purchase many Courses. A Course can be purchased by many Students.

• We must permanently track exactly how much the student paid for the course at checkout.

4. Phase 2: Entity Identification & ER Diagram (Chapters 5 & 6) #

• Users to Courses (Creation) -> 1:N

• Courses to Lessons -> 1:N

• Users to Courses (Purchasing) -> M:N

*(The M:N relationship demands a Junction Table! We will call it Purchases).*

5. Phase 3: Physical Schema Design (Chapters 7, 8, 12, 13) #

-- 1. The Strong Entity (Users)
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL,
    role ENUM('Student', 'Instructor') DEFAULT 'Student',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. The Course Entity (1:N from Users)
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    instructor_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    current_price DECIMAL(10,2) NOT NULL CHECK (current_price >= 0),
    
    -- Enforcing Referential Integrity!
    FOREIGN KEY (instructor_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- 3. The Lesson Entity (1:N from Courses)
CREATE TABLE lessons (
    lesson_id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    content_text TEXT,
    video_url VARCHAR(255),
    
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

-- 4. The Junction Table (M:N between Users and Courses)
CREATE TABLE purchases (
    purchase_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    
    -- The Snapshot Pattern (Chapter 17) to permanently lock financial data!
    amount_paid DECIMAL(10,2) NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (student_id) REFERENCES users(user_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    
    -- Ensuring a student cannot buy the same course twice!
    UNIQUE(student_id, course_id)
);

6. Phase 4: Performance & Scalability Planning (Chapters 14, 15, 19) #

• Indexing: We explicitly add a B-Tree Index on lessons(courseid) because the application will constantly run SELECT * FROM lessons WHERE courseid = ? to load the curriculum.

• Denormalization: The CEO wants to show the "Total Students Enrolled" on the course card. Running a COUNT() on the purchases table for 1 million students will cause CPU lag. We intentionally violate 3NF by adding an enrollmentcount INT DEFAULT 0 column directly to the courses table, updating it via a Database Trigger (Chapter 25 of SQL Fundamentals) whenever a purchase is made.

• The Web Application connects to this database using a heavily restricted webappuser.

• The user has NO DROP TABLE permissions.

• All backend Node.js code utilizes Parameterized Queries (Prepared Statements) to mathematically neutralize SQL Injection.

• Passwords are securely hashed via bcrypt in the password_hash column.

8. Final Review #

• You eliminated Data Redundancy and achieved Third Normal Form.

• You mathematically enforced Business Rules using Foreign Keys and CHECK constraints.

• You anticipated massive data growth by implementing Indexing and Denormalization strategies.

• You locked the system down against cyber threats.

You are no longer a beginner drawing boxes on a whiteboard. You are a highly capable Database Architect.

9. Conclusion and Next Steps #

1. 1. Master an RDBMS: Pick either MySQL or PostgreSQL and master its specific nuances, engines (InnoDB), and advanced configuration files.

1. 2. Learn System Architecture: Database design is just one piece of System Design. Study how Load Balancers, Microservices, and Message Queues (RabbitMQ/Kafka) interact with databases.

1. 3. Explore Cloud Infrastructure: Learn how to deploy and manage managed databases on AWS (RDS, DynamoDB) or Google Cloud.

Thank you for choosing this platform for your architectural journey. Keep planning, keep optimizing, and welcome to the highest echelons of software engineering.