Let's Learn AdonisJS 6: Understanding our Database Schema - Adocasts

Sign Up to Track Progress

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, 24
Duration: 9m 34s
Comments: 2

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

Get the Code

Download or explore the source code for this lesson on GitHub

Database Diagram

Database Diagram

Join The Discussion! (2 Comments)

Please sign in or sign up for free to join in on the dicussion.

frp

Commented 1 year ago

I want more! With a rebel yell, I cry "More! More! More!"
:)
Good stuff!

3

Please sign in or sign up for free to reply
1. tomgobich
  
  Commented 1 year ago
  
  Lol, thank you!! :D
  
  0
  
  Please sign in or sign up for free to reply

Playing Next Lesson In

100 seconds

Let's Learn AdonisJS 6 #4.1

Understanding our Database Schema

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.