Rocket Academy Bootcamp
  • 🚀Welcome to Bootcamp!
  • 🛠️Logistics
    • Course Schedules
    • Course Methodology
    • Required Software
    • LinkedIn Education Badge
  • 📚General Reference
    • Naming, Casing, and Commenting Conventions
    • VS Code Tips
    • Recommended Resources
  • 🪨0: Foundations
    • 0.1: Command Line
    • 0.2: Git
      • 0.2.1: Branches
    • 0.3: GitHub
      • 0.3.1: Pull Requests
    • 0.4: JavaScript
      • 0.4.1: ES6
      • 0.4.2: Common Syntax
      • 0.4.3: Reference vs Value
      • 0.4.4: Classes
      • 0.4.5: Destructuring and Spread Operator
      • 0.4.6: Promises
        • 0.4.6.1: Async Await
    • 0.5: Node.js
      • 0.5.1: Node Modules
      • 0.5.2: NPM
      • 0.5.3: Nodemon
  • 🖼️1: Frontend
    • 1.1: HTML
    • 1.2: CSS
      • 1.2.1: Layout
    • 1.3: React
      • Styling in ReactJs
      • Using Styling Libraries with React
      • React Deployment
    • 1.E: Exercises
      • 1.E.1: Recipe Site
      • 1.E.2: Portfolio Page
      • 1.E.3: World Clock
      • 1.E.4: High Card
      • 1.E.5: Guess The Word
    • 1.P: Frontend App
  • 🏭2: Full Stack
    • 2.1: Internet 101
      • 2.1.1: Chrome DevTools Network Panel
      • 2.1.2: HTTP Requests and Responses
    • 2.2: Advanced React
      • 2.2.1: AJAX
      • 2.2.2: React Router
      • 2.2.3: useContext
      • 2.2.4: useReducer
      • 2.2.5: Environmental Variables
      • 2.2.6: React useMemo - useCallback
    • 2.3: Firebase
      • 2.3.1: Firebase Realtime Database
      • 2.3.2: Firebase Storage
      • 2.3.3: Firebase Authentication
      • 2.3.4: Firebase Hosting
      • 2.3.5: Firebase Techniques
    • 2.E: Exercises
      • 2.E.1: Weather App
      • 2.E.2: Instagram Chat
      • 2.E.3: Instagram Posts
      • 2.E.4: Instagram Auth
      • 2.E.5: Instagram Routes
    • 2.P: Full-Stack App (Firebase)
  • 🤖3: Backend
    • 3.1: Express.js
      • 3.1.1 : MVC
    • 3.2: SQL
      • 3.2.1: SQL 1-M Relationships
      • 3.2.2: SQL M-M Relationships
      • 3.2.3: SQL Schema Design
      • 3.2.4: Advanced SQL Concepts
      • 3.2.5: SQL - Express
      • 3.2.6: DBeaver
    • 3.3: Sequelize
      • 3.3.1: Sequelize One-To-Many (1-M) Relationships
      • 3.3.2: Sequelize Many-To-Many (M-M) Relationships
      • 3.3.3: Advanced Sequelize Concepts
      • 3.3.4 Database Design
    • 3.4: Authentication
      • 3.4.1: JWT App
    • 3.5: Application Deployment
    • 3.E: Exercises
      • 3.E.1: Bigfoot JSON
      • 3.E.2: Bigfoot SQL
      • 3.E.3: Bigfoot SQL 1-M
      • 3.E.4: Bigfoot SQL M-M
      • 3.E.5: Carousell Schema Design
      • 3.E.6: Carousell Auth
    • 3.P: Full-Stack App (Express)
  • 🏞️4: Capstone
    • 4.1: Testing
      • 4.1.1: Frontend React Testing
      • 4.1.2: Backend Expressjs Testing
    • 4.2: Continuous Integration
      • 4.2.1 Continuous Deployment (Fly.io)
      • 4.2.2: Circle Ci
    • 4.3: TypeScript
    • 4.4: Security
    • 4.5: ChatGPT for SWE
    • 4.6: Soft Skills for SWE
    • 4.P: Capstone
  • 🧮Algorithms
    • A.1: Data Structures
      • A.1.1: Arrays
        • A.1.1.1: Binary Search
        • A.1.1.2: Sliding Windows
      • A.1.2: Hash Tables
      • A.1.3: Stacks
      • A.1.4: Queues
      • A.1.5: Linked Lists
      • A.1.6: Trees
      • A.1.7: Graphs
      • A.1.8: Heaps
    • A.2: Complexity Analysis
    • A.3: Object-Oriented Programming
    • A.4: Recursion
    • A.5: Dynamic Programming
    • A.6: Bit Manipulation
    • A.7: Python
  • 💼Interview Prep
    • IP.1: Job Application Strategy
    • IP.2: Resume
    • IP.3: Portfolio
Powered by GitBook
On this page
  • Learning Objectives
  • Introduction
  • Entity Relationship Diagram (ERD)
  • DrawSQL
  1. 3: Backend
  2. 3.2: SQL

3.2.3: SQL Schema Design

Previous3.2.2: SQL M-M RelationshipsNext3.2.4: Advanced SQL Concepts

Learning Objectives

  1. SQL schema design is the process of articulating what SQL tables, columns and relationships we need for a given app's database.

  2. Know how to draw an ERD (entity relationship diagram) to visualise SQL tables, columns and relationships

  3. Know how to use DrawSQL to draw ERD diagrams

Introduction

SQL schema design is the process of articulating what SQL tables, columns and relationships we need for a given app's database. There is on how to optimise SQL schemas, but for now we will focus on building simple schemas that solve our problems without worrying too much about theory.

We will follow 3 rules when designing our schema:

  1. Every table must have a unique ID column

  2. Every cell may only have 1 piece of data. No data structures such as arrays or hash tables.

  3. Only IDs can be duplicated in multiple tables via foreign keys. Everything else should be referenced by foreign key.

Entity Relationship Diagram (ERD)

An entity relationship diagram (ERD) is a diagram that depicts SQL tables, columns and the relationships between them. Apps that use SQL databases typically have database ERDs to help engineers plan ahead. Apps that use NoSQL databases typically also have some form of but SQL ERDs are more common and standardised.

Below is a sample ERD for Rocket's Bigfoot exercises created with .

Notice the 3 entities we created for our app: sightings, comments and categories in their respective tables. Notice there is a 4th table SightingCategories between Sightings and Categories; This is the junction table in the M-M relationship. We will always include junction tables in our ERDs even if we do not create models for them in our apps.

Notice the lines between the tables; These represent the relationships between them. Each line represents a relationship, and the ends of the lines communicate whether the relationships are 1-1 or 1-M. 1-1 lines (we don't have any) have no special ends. 1-M lines have a "crow's foot" at the end of the line that touches the "many" table in the 1-M relationship.

Instead of depicting M-M relationships with lines that have crow's feet at both ends, a common practice in ERDs is to split M-M relationships into 2 1-M relationships, like what we did with Sightings, SightingCategories and Categories. This clarifies exactly what tables will be in the database.

Notice how our Bigfoot ERD satisfies our rules above:

  1. Every table has a unique id column that is its primary key

  2. Every cell only has 1 piece of data

  3. Only IDs are duplicated across multiple tables as foreign keys; all other data only exists in its own table

DrawSQL

Rocket recommends we use to create ERDs because of its simple and ERD-specific interface. We can quickly create the tables, columns and relationships we need, and share our ERDs easily with links or images. We will use DrawSQL for upcoming exercises that involve ERDs.

🤖
DrawSQL
extensive theory
schema plan
DrawSQL
Database ERD for Rocket's Bigfoot exercises
DrawSQL interface for creating ERDs