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.
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