Understanding our Database Schema
In this lesson, we'll walk through how to understand a database schema diagram. We'll then discuss the schema we'll be working with throughout this series; describing the tables, columns, data types, and relationships.
- Author
- Tom Gobich
- Published
- Feb 21
- Duration
- 9m 35s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Understanding our Database Schema
-
(upbeat music)
-
So before we dive in,
-
let's first define what a schema is for a database.
-
So it's essentially going to define the overall structure
-
of the database itself.
-
It's gonna contain the table definitions,
-
the columns of each of those tables,
-
the relationships between the various tables itself,
-
depending on how in-depth
-
your particular structuring diagram application is,
-
it could contain additional information
-
like the particular column type for each of those columns,
-
whether or not they're nullable,
-
whether or not they're unique, so on and so forth.
-
Essentially defining as granular as you wanna go,
-
the particular definition for your database,
-
specifically for your application.
-
So I've already gone ahead and diagrammed out the database
-
that we're going to be working with throughout this series.
-
I use dbdiagram.io to do this,
-
and this is the output of that diagram.
-
So each rectangle here represents a table.
-
So we have a roles table, users table, watch list table,
-
crew movies, movies, movie statuses,
-
cynists, and cast movies.
-
Now, cynists is a weird word.
-
I had never heard of it before
-
actually starting out the structure diagram,
-
but if we take a look at the definition of it,
-
it's essentially a filmmaker.
-
It's an enthusiast or a devotee of filmmaking or movies.
-
So it's essentially a single word that we can use
-
to reference crew members as well as cast members,
-
so that we can have them all confined within one table
-
because sometimes you have a director
-
who's also a cast member or a writer
-
who's also a cast member,
-
so they're mix and matchable in that sense.
-
So let's hide our browser back away.
-
So that's what our cynists table is going to contain.
-
It's going to contain our crew members, our cast members.
-
Each crew member is going to have a unique ID.
-
This is going to serve as our primary key.
-
That primary key is a unique identifier for our database,
-
similarly to how we're using the file name
-
within our application at this present point in time
-
to uniquely identify each one of the movies
-
inside our application.
-
But this ID primary key within each of these tables
-
that you see is specific to our database.
-
And our database is going to be in charge
-
of assigning an ID per record
-
that we insert into our database.
-
So we don't need to worry about
-
whether or not an ID is unique.
-
Our database will take care of that automatically for us
-
as we insert each record.
-
It's kind of just going to be an index that starts at one.
-
Each time that we insert a new record,
-
it's going to go from one to two,
-
two to three, three to four, so on and so forth,
-
making sure that each one of those records IDs
-
are unique in each one of our separate tables.
-
So our synapse are going to have an ID,
-
a first name, last name, a headshot URL,
-
which will be a URL for an image
-
of the particular person's headshot.
-
They created that, so when they were inserted
-
into the database and then updated that.
-
So when the record was last updated inside of the database,
-
by default, the created at and updated at
-
whenever they're first entered
-
are going to be the same value.
-
And you'll see that the created at, updated at, and ID
-
are shared columns throughout each one of the tables
-
that we have, our roles, users, watch lists, crew movies,
-
so on and so forth, all have an ID marked with the key here
-
to denote that it is a primary key,
-
and a created at and updated at column,
-
both of type timestamp.
-
You'll also see that the first name, last name,
-
headshot URL, as well as various other columns
-
here within our schema, have a VARCHAR
-
with a number inside of parentheses inside of it.
-
The VARCHAR is a database safe type for strings, essentially.
-
And then the number inside of the parentheses
-
is the length that we want to allow.
-
So for example, our first name and last name
-
can be up to, but not more than 100 characters.
-
Our headshot URL can be up to, but not more than 255.
-
The name for our movie status can be up to,
-
but not over 50 characters, so on and so forth.
-
And then for our abstract here within our movie itself,
-
we have a text column type.
-
The text column type is just essentially
-
a really long form version of a string.
-
It's not particularly limited
-
in the sense that our VARCHARs are.
-
And then we have our ints, which our primary keys are,
-
but so too our relationship-based information,
-
so like status ID relating to our movie statuses,
-
writer ID going to our cynists,
-
director ID going to our cynists, and so on and so forth.
-
The int type is going to be a number.
-
However, it's going to be a whole number.
-
So one, two, three, it's not going to include 1.1
-
or decimal-based types like that.
-
It's going to be specifically whole numbers.
-
Okay, so now that we have a sense of everything going on
-
inside of our schema with how we're defining things,
-
let's talk about the actual definition itself
-
for our schema.
-
So we have this split into really two different sections.
-
We have this section down here at the bottom
-
encompassing our movie statuses,
-
movies, crew movies, cynists, cast movies.
-
This is the portion that we're going to be focusing on
-
first in this series.
-
And then we have our roles, users, and watch lists
-
that we'll use within this series
-
to kind of wrap everything up with a bow
-
to cover how you would approach an entire feature
-
within our application here.
-
So these bottom ones are going to be in charge
-
of teaching us how we can define relationships,
-
migrations, models, queries, so on and so forth
-
within our application.
-
And then we'll wrap things up with a bow
-
with our roles, users, and watch lists
-
by covering how we would approach
-
defining those particular features
-
inside of our application.
-
Now we haven't talked about relationships yet,
-
but you'll notice that each of these tables
-
has a line pointing to and from it.
-
Relationships are defined by taking a records
-
and a tables primary key, so that ID column,
-
and referencing it elsewhere inside of another table.
-
That creates a relationship.
-
Now there's various different types of relationships.
-
You have one-to-one, one-to-many, or many-to-one,
-
depending on which side of the relationship
-
you're looking at, and then many-to-many as well.
-
And within our schema, these relationships
-
are defined by these lines.
-
So within our movie statuses, our ID for our movie status
-
is pointing to the status ID column on our movie itself,
-
essentially saying that the movie is assigned a status
-
of whatever the value is matching the ID
-
inside of our movie statuses table.
-
So for example, our movie statuses table
-
might have status of released, post-production,
-
production, writing, or various other things like that.
-
So if a movie was released, it might have an ID of one.
-
If a movie's in post-production,
-
it might have an ID of two, so on and so forth.
-
So if our particular movie record is released,
-
it's going to have a status ID of one,
-
matching the released record
-
inside of our movie statuses table.
-
If the movie's in post-production,
-
our status ID would be two,
-
matching the post-production record
-
inside of our movie statuses table.
-
If you're looking at this from our movie statuses table,
-
this would be a one-to-many relationship
-
because one record inside of our movie statuses table
-
can have many movies.
-
But if you're looking at this from the movie side,
-
it's going to be a many-to-one
-
because we can have many movies
-
pointing to one particular movie status.
-
We have the same thing going on with our users and our roles
-
and our users and the watch list.
-
And our watch list also matches to a movie as well.
-
And then within our movie,
-
we also have a writer ID and a director ID,
-
both pointing to a cynist's ID.
-
But we also have a many-to-many relationship
-
between our movies and our cynists as well,
-
denoted via our crew movies and cast movies
-
pivot tables.
-
Pivot tables are intermediary tables.
-
They stand in between the relationship
-
to define what two records go together,
-
in addition to various other information we may need.
-
Particularly, we might have a sort order
-
denoting who the top build cast member was
-
for the particular movie,
-
so on and so forth, going down the list as such.
-
So for example,
-
if we take a look at our crew movies pivot table here,
-
we have both a cynist ID
-
pointing to one particular cynist member,
-
and then we also have a movie ID
-
pointing to one particular movie.
-
A movie can have many crew members,
-
and a crew member can be a part of many movies.
-
This pivot table is how we can define
-
both of those together.
-
So let's walk through a quick example
-
of our crew movies many-to-many relationship,
-
because this can be a little bit confusing.
-
So we have our crew movies here.
-
We have an ID, our cynist ID, movie ID, and title.
-
For our movies,
-
let's say that we have one with an ID of one, two, and three.
-
And then for our cynists,
-
let's say that we have IDs of 10, 11, and 12.
-
Our many-to-many table here
-
essentially allows us to say that cynist 10
-
has been a part of multiple movies.
-
So one, two, and three, for example.
-
Cynist 11 might be part of just movies one and two,
-
and cynist 12 might just be a part of movie three.
-
So we have cynist ID of 10, movie ID of one.
-
Maybe their title for this was writer.
-
And then we would have cynist 10 on movie two.
-
Maybe they were somebody's assistant
-
or something for that movie.
-
And then we would have cynist 10 on movie three.
-
Maybe they were a writer again.
-
Each of these records would get a unique ID,
-
so we would have one, two, and three there.
-
And so with this,
-
we've defined that cynist number 10
-
has been a part of movies one, two, and three.
-
Conversely, we've also noted that movie one
-
has a cast member of cynist 10,
-
movie two has a cast member of cynist 10,
-
and movie three has a cast member of cynist 10.
-
And with each of these,
-
we've assigned that cynist their unique role
-
for that particular movie of writer, assistant, and writer.
-
Now for cynist 11, they were only a part of two movies.
-
So we can add in our IDs of four and five.
-
For cynist 11, they were in part of one.
-
Maybe they just got coffee.
-
And then on movie two, maybe they got the bagels.
-
So in addition to cynist 10,
-
we've now also defined that cynist 11
-
has been a part of movie one and movie two,
-
and their various roles for those two movies.
-
And now we also have the flip side of that
-
where movie one now has two different cast members.
-
They have both cynist 10 and cynist 11.
-
So our many-to-many table really just allows us
-
to mash together two different tables
-
to say that they both relate to one another,
-
and we can define additional information
-
on how they specifically relate to one another as well.
-
If how these tables relate to one another
-
isn't quite making sense yet, don't worry.
-
That's still a good step away.
-
We have to define everything first within our application,
-
get our actual tables created,
-
along with all of their columns,
-
get data inside of each one of those tables,
-
and then we can start to look at
-
how they relate to one another.
-
So we're gonna step through it step-by-step.
-
So if things aren't quite making sense yet,
-
don't fret, we'll get there.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 7s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (2 Comments)
Please sign in or sign up for free to join in on the dicussion.
frp
I want more! With a rebel yell, I cry "More! More! More!"
:)
Good stuff!
Please sign in or sign up for free to reply
tomgobich
Lol, thank you!! :D
Please sign in or sign up for free to reply