Database Design & Modeling - SquareShift Academy

Database Design Principles & Normalization

Foundation + Theory

Overview

Master the fundamental principles of database design including conceptual, logical, and physical modeling. Learn normalization theory from 1NF through 5NF, understand ACID properties, and discover when to denormalize for performance. Build skills in entity-relationship modeling, data integrity constraints, and creating maintainable database schemas that scale with business requirements.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
Database Design Basics	Microsoft	Comprehensive guide to database design principles covering purpose determination, information organization, table division, key specification, and design refinement.	Beginner	Required	Start Learning
Database Normalization Explained	Decomplexify	Clear explanation of normalization rules from First to Fifth Normal Form (1NF-5NF) with practical examples and when to apply each form.	Intermediate	Required	Start Learning
ACID Properties of Transactions	Code-Verse	Essential understanding of Atomicity, Consistency, Isolation, and Durability properties with banking examples and comparison to BASE properties.	Beginner	Required	Start Learning
Entity-Relationship Modeling Tutorial	Lucidchart	Complete guide to creating ER diagrams including entities, attributes, relationships, cardinality, and best practices for conceptual modeling.	Intermediate	Required	Start Learning
Data Normalization Practical Guide	Caspio	Hands-on example of transforming poorly structured tables into well-normalized relational models with primary and foreign keys.	Beginner	Optional	Start Learning
Database Theory and Practice	Stanford Online	Academic depth in relational database theory, design principles, and practical implementation strategies from Stanford University.	Advanced	Optional	Start Learning

Database Design Foundation Project

Task Manager Database Schema Design

Create conceptual ER diagram for Task Manager entities (Users, Tasks, Categories, Teams)
Define entity attributes, primary keys, and identify all relationships with proper cardinality
Apply normalization principles to eliminate redundancy and ensure data integrity
Design logical schema with proper constraints, indexes, and business rules
Document design decisions including trade-offs between normalization and performance
Create data dictionary with detailed field definitions and validation rules

Start Project

SQL Databases & Advanced Querying

SQL Mastery

Overview

Develop mastery in SQL databases including PostgreSQL, MySQL, and SQL Server. Learn advanced querying techniques including complex joins, subqueries, window functions, and Common Table Expressions (CTEs). Master stored procedures, triggers, and views while understanding query optimization and execution plans for high-performance database operations.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
Relational Database Certification	freeCodeCamp	Comprehensive SQL course covering database design, complex queries, joins, functions, and advanced concepts with hands-on PostgreSQL practice.	Intermediate	Required	Start Learning
PostgreSQL Complete Course	Udemy	In-depth PostgreSQL training covering installation, administration, advanced queries, performance tuning, and enterprise features.	Intermediate	Required	Start Learning
Advanced SQL Techniques	DataCamp	Master advanced SQL concepts including window functions, CTEs, recursive queries, and performance optimization techniques.	Advanced	Required	Start Learning
MySQL Tutorial for Beginners	ProgrammingKnowledge	Complete MySQL course with 58 lessons covering installation, MySQL Workbench, and fundamental to advanced SQL operations.	Beginner	Optional	Start Learning
SQL Performance Tuning	SQL Server Central	Comprehensive guide to SQL performance optimization including query plans, indexing strategies, and database tuning best practices.	Advanced	Optional	Start Learning
PostgreSQL Documentation	PostgreSQL	Official PostgreSQL documentation with comprehensive tutorials, advanced features, and enterprise database concepts.	All Levels	Reference	Start Learning

Advanced SQL Implementation Lab

Task Manager SQL Database Implementation

Implement complete PostgreSQL schema with tables, constraints, and relationships
Create advanced queries using joins, subqueries, and window functions for task analytics
Build stored procedures for complex business logic (task assignment, deadline notifications)
Design and implement database views for different user roles and reporting needs
Write triggers for audit logging and data validation enforcement
Create comprehensive test suite with sample data and query performance benchmarks

Start Lab

NoSQL Databases & Document Modeling

NoSQL Architecture

Overview

Explore NoSQL database paradigms including document stores, key-value pairs, column-family, and graph databases. Master MongoDB for document modeling, understand CAP theorem implications, and learn when to choose NoSQL over SQL. Develop skills in schema-less design, aggregation pipelines, and horizontal scaling strategies for modern web applications.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
MongoDB Complete Course	MongoDB University	Official MongoDB course covering document modeling, CRUD operations, aggregation framework, indexing, and production deployment strategies.	Intermediate	Required	Start Learning
NoSQL Database Fundamentals	Udemy	Comprehensive overview of NoSQL database types including document, key-value, column-family, and graph databases with practical examples.	Beginner	Required	Start Learning
MongoDB Data Modeling	MongoDB University	Advanced course on MongoDB schema design patterns, embedding vs referencing strategies, and performance optimization for document databases.	Advanced	Required	Start Learning
Redis Fundamentals	Redis University	Learn Redis key-value store for caching, session management, and real-time applications with hands-on exercises and use cases.	Intermediate	Optional	Start Learning
DynamoDB Modeling Guide	AWS	Master Amazon DynamoDB single-table design patterns, partition keys, and NoSQL modeling for serverless applications.	Advanced	Optional	Start Learning
CAP Theorem and NoSQL	Database Journal	Understand the CAP theorem implications for distributed systems and how different NoSQL databases handle consistency, availability, and partition tolerance.	Advanced	Reference	Start Learning

NoSQL Implementation Workshop

Task Manager NoSQL Implementation

Design MongoDB document schema for flexible task management with embedded and referenced data
Implement CRUD operations using MongoDB Node.js driver with proper error handling
Build complex aggregation pipelines for task analytics and reporting dashboards
Create Redis caching layer for frequently accessed task data and user sessions
Compare performance and use cases between SQL and NoSQL implementations
Design horizontal scaling strategy and sharding approach for large-scale deployment

Start Workshop

Performance Optimization & Indexing

Performance Engineering

Overview

Master database performance optimization techniques including strategic indexing, query plan analysis, and caching strategies. Learn to identify bottlenecks using profiling tools, understand different index types (B-tree, hash, partial), and implement monitoring solutions. Develop skills in database tuning, connection pooling, and scaling strategies for high-performance applications.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
Database Indexing Explained	Code-Verse	Comprehensive guide to database indexes including B-Tree, hash, and full-text indexes with best practices and performance implications.	Intermediate	Required	Start Learning
SQL Query Optimization	Caleb Curry	Practical tips for writing efficient SQL queries including proper use of LIMIT, avoiding SELECT DISTINCT, and optimizing wildcards.	Beginner	Required	Start Learning
PostgreSQL Performance Tuning	PGTune	Advanced PostgreSQL performance optimization including configuration tuning, query analysis, and monitoring with real-world examples.	Advanced	Required	Start Learning
Database Monitoring and Profiling	Datadog	Learn database monitoring strategies, key metrics to track, and tools for identifying performance bottlenecks and slow queries.	Intermediate	Required	Start Learning
Connection Pooling Best Practices	HikariCP	Understanding database connection pooling concepts, configuration options, and implementation strategies for optimal resource utilization.	Intermediate	Optional	Start Learning
Database Caching Strategies	AWS	Comprehensive guide to caching patterns including cache-aside, write-through, and write-behind strategies for improved database performance.	Advanced	Optional	Start Learning

Performance Optimization Challenge

Task Manager Performance Optimization

Analyze current Task Manager database performance using EXPLAIN ANALYZE and query profiling
Design and implement strategic indexes for frequently accessed queries (user tasks, due dates, categories)
Optimize complex queries using query rewriting, subquery elimination, and proper join strategies
Implement Redis caching layer for frequently accessed data with appropriate TTL strategies
Set up database connection pooling and configure optimal pool sizes for application load
Create monitoring dashboard to track key performance metrics and establish performance baselines

Start Challenge

Database Security & Backup Strategies

Security + Reliability

Overview

Master critical database security practices including authentication, authorization, encryption, and audit logging. Learn comprehensive backup and disaster recovery strategies, understand RPO/RTO requirements, and implement automated backup testing. Develop expertise in SQL injection prevention, data privacy compliance, and security monitoring for production database environments.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
Database Security Fundamentals	OWASP	Comprehensive guide to database security including SQL injection prevention, authentication, authorization, and data encryption best practices.	Intermediate	Required	Start Learning
SQL Injection Prevention	PortSwigger	Learn to identify and prevent SQL injection attacks using parameterized queries, input validation, and prepared statements with hands-on examples.	Beginner	Required	Start Learning
PostgreSQL Backup and Recovery	PostgreSQL.org	Official documentation covering fundamental backup principles like SQL dumps, recovery, and backup strategies.	Intermediate	Required	Read Documentation
PostgreSQL Security Guide	PostgreSQL	Official PostgreSQL security documentation covering authentication methods, SSL configuration, row-level security, and audit logging.	Advanced	Required	Start Learning
GDPR and Database Compliance	EU GDPR	Understanding data privacy regulations, implementing data anonymization, managing consent, and ensuring compliance in database design.	Intermediate	Optional	Start Learning
Database Encryption Strategies	AWS	Learn encryption at rest and in transit, key management strategies, and implementation of database encryption using cloud and on-premise solutions.	Advanced	Optional	Start Learning

Security & Backup Implementation

Task Manager Security & Disaster Recovery

Implement comprehensive authentication and authorization system with role-based access control
Secure all database queries using parameterized statements and input validation to prevent SQL injection
Configure SSL/TLS encryption for database connections and implement data encryption at rest
Design and implement automated backup strategy with daily, weekly, and monthly retention policies
Create disaster recovery plan with RTO/RPO targets and test restoration procedures
Set up audit logging and security monitoring to track database access and suspicious activities

Start Implementation

Database Integration & Migration

Integration + DevOps

Overview

Master database integration patterns for modern applications including ORM/ODM usage, migration strategies, and version control. Learn API integration patterns, data synchronization techniques, and microservices database patterns. Develop skills in database DevOps, automated schema migrations, and integration testing strategies for reliable database deployments.

Learning Resources

Course Title	Provider	Description	Level	Mandatory	Action
Prisma ORM Complete Guide	Prisma	Modern ORM for Node.js and TypeScript featuring type safety, database migrations, and powerful query builder with automated schema generation.	Intermediate	Required	Start Learning
Database Migration Strategies	The Seattle Data Guy	Key considerations for successful database migration including schema validation, data reconciliation, performance benchmarking, and rollback strategies.	Intermediate	Required	Start Learning
Flyway Database Migrations	JetBrains	Learn automated database migrations using Flyway with JPA models in Java applications, including versioned SQL script generation.	Intermediate	Required	Start Learning
Mongoose ODM for MongoDB	Mongoose	Comprehensive guide to Mongoose ODM including schema definition, validation, middleware, and advanced querying patterns for Node.js applications.	Intermediate	Required	Start Learning
Database Change Management	Liquibase	Best practices for managing database changes in collaborative environments including version control, code quality checks, and cross-team communication.	Intermediate	Optional	Start Learning
Microservices Database Patterns	Martin Fowler	Understanding database patterns for microservices including database-per-service, event sourcing, and distributed transaction management.	Advanced	Optional	Start Learning

Integration & Migration Capstone

Task Manager Database Integration & DevOps

Integrate Task Manager with both Prisma (PostgreSQL) and Mongoose (MongoDB) implementations
Create automated migration scripts for schema updates with version control integration
Implement database seeding and test data management for different environments
Set up CI/CD pipeline with automated database migration and rollback capabilities
Build comprehensive integration tests covering database operations and API endpoints
Document migration procedures and create disaster recovery playbooks for production deployment

Start Capstone