3.2.3: SQL Schema Design

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 extensive theory 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 schema plan but SQL ERDs are more common and standardised.

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

Database ERD for Rocket's Bigfoot exercises

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 DrawSQL 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 interface for creating ERDs