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.

Created by: @tomgobich
Published: 1 year ago

Database Diagram

Join the Discussion 2 comments

@frp
1 year ago

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

3
1. Responding to frp
  
  @tomgobich
  1 year ago
  
  Lol, thank you!! :D
  
  0

Transcript

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.

Let's Learn AdonisJS 6

113 Lessons

14h 15m

Introduction

Fundamentals

Building Views with EdgeJS

Database and Lucid ORM Basics

Lucid ORM Relationships

Working With Forms

Authentication & Middleware

Filtering and Paginating Queries

User Watchlist

User Profiles

Admin Panel

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.