3.3.6: SQL Schema Design


SQL schema design is designing SQL table structures and the relationships between them such that data can be stored and retrieved easily and efficiently. When we model real data in Express, each data "model" is usually associated with at least 1 other data model. Using foreign keys we can associate any table to any other table, but how do we decide which information to put in which tables?
There are a few "hard" rules when designing a SQL schema:
  1. 1.
    IDs must be unique.
  2. 2.
    Only store 1 conceptual "piece" of data in a cell. This means no arrays or JS Objects.
  3. 3.
    Avoid duplicating data in multiple tables. For example if name exists in 1 table, use a foreign key to reference it instead creating a 2nd name column for the same data in a 2nd table.
  4. 4.
    Tables are related to each other through primary key - foreign key columns. Always use primary keys to reference rows in another tables.
There will always be well-considered exceptions to these rules, but they are relatively rare. The most common is when dealing with data that is not relational to the rest of the system. Something like a user dice roll may not be relational enough to warrant sticking to the rules above.

Entity Relationship Diagram (ERD)

This image is an example of what an ERD might look like. The ERDs we will create at Rocket will be simpler, with fewer symbols (described below). Our ERDs at Rocket will also include data types, which the ERD in this image does not.
An ERD (Entity Relationship Diagram) is a drawing that lists the relationships and types of relationships between data. The purpose of an ERD is to visually represent how data relates to itself.
There are many different flavours of ERD, used at various stages of planning, analysing and implementing a system. We will mostly be using it as a sketching tool before writing any SQL.
We will not be using an ERD to specify any column data types or low-level SQL syntax. Our ERD will be a kind of pseudocode for our CREATE TABLE SQL statements.
Our ERD sits one conceptual level above the database. It talks about the kinds of data our app deals with.

Drawing an ERD

There are online tools that can be used to draw an ERD but pencil and paper work just as well. The following drawing is an example of how to represent one-to-many, many-to-many, and one-to-one relationships in an ERD.
How to represent one-to-many, many-to-many, and one-to-one relationships in an ERD
Note that there are a comprehensive set of "official" ERD relationship symbols, but for our purposes, these are not necessary). See more here.​

Lucid Chart

Past students have found Lucid Chart an effective tool for creating ERDs. Feel free to use any tool you prefer. Below is a screenshot of a sample ERD with Lucid Chart. Use the Entity Relationship toolbar on the left panel to create tables and relationships.
We can customise the relationships between tables by selecting the relationship line and changing the endpoint symbols using the top toolbar.
Past students have found this useful in explaining the functionalities behind LucidChart and using it for importing and exporting ERDs:​
Copy link
On this page
Entity Relationship Diagram (ERD)
Drawing an ERD
Lucid Chart