Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I will go to model for this is less than 4.3 implement models to In this lesson we're gonna review are updated er de diagram. I've added a building table to the diagram. This improves room management because the building can have many rooms, and then we can just have the address of the building once
00:19
on the building table. I've also added column names to the E R D diagram. I have also added deleted at calm to course and user. This is a column that will help protect against accidental deletion. When the user first attempts to delete a course or a user, the deleted at column fills in,
00:39
so the record is still there. And if we really need to delete the record, then we can detect that the deleted at columnist filled in and then actually delete the record. And then we could restore a record by removing the deleted at date data.
00:55
So we also need to implement the columns and run the migration. So let's take a quick look at that E R D diagram.
01:03
Okay, here's the updated er de diagram that I created. This will be available for download from the resource is. But let's step through some of the things that we are seeing on this diagram that weren't there before.
01:15
I've added the column names to many of the tables. Now we are using string a lot. You wouldn't necessarily have to do that.
01:22
For example, you might use a manager for the phone number and for the ZIP code, but we're just keeping it a little bit easy here and just using string. And many of the instances
01:34
I've added he created out updated that
01:36
and the deleted at column on both the user and the course. So what this will help with is avoid an accidental dilation. For example, if someone deletes a user, the record at first will get a date time stamp and then queries that query out. Active records
01:55
will require that
01:57
column to be no.
01:59
Then you can go to a trash area where you can delete the user or the course again. So you effectively create a scenario in which a front end user has to delete the record twice, which helps affirm that that's really what they want to do and not a one time accident.
02:16
Another item added to this year. D diagram is a relationship between the great and the course, so we can tell what
02:24
course the great belong to.
02:28
And
02:30
something to note on this er de diagram is that this is the name of the relationship. For example, grades is not. A column is just indicating that the course has a great relationship.
02:45
So let's go ahead and get started with implementing these migrations.
02:49
All right, so let's get started. We need to add this building model and migration. So I'm in a shells session with my Bagram machines. Someone hit up,
02:58
go to some of my previous commands,
03:00
have a make model. So I'm gonna change my last one, which was room to building
03:06
and hit. Enter
03:09
next. I'm gonna go into the folder where the stuff is
03:14
being generated.
03:15
And again it's being generated on the virtual machine in the home vagrant level directory. And that's being sink to my shared directory on my host.
03:27
So changed changes that occur and one occurring the other as well.
03:31
So let's go to my migrations. Let's go and start filling in our columns.
03:37
So let's make sure we have all our users,
03:40
all our user data actress one I just to city state zip code
03:46
created at Deleted at
03:53
to add the deleted at column. We're going to use a
03:57
migration command table,
04:01
soft elites,
04:04
and that's gonna add the deleted at column that we need.
04:12
So that appears to be everything we need in the user's table. So let's go ahead and make sure everything's in the grades table that we need.
04:20
Be sure to save
04:23
you, forget to save. Then, of course, when you migrate, the change won't happen.
04:28
So the grades we have the time stamps we have, which will be the time since will be the created and the updated columns.
04:34
You have great score,
04:36
right? Yeah, a great score letter. Great. So that one's good to go.
04:44
Let's open our user types
04:46
and our usual type. We need a title
05:00
on. We need a
05:02
description
05:15
and we have the time steps. So that finishes that one again. Remember, like this user courses is the name of a relationship and not a column.
05:27
So let's go ahead and move on to the next one,
05:30
which is the user course,
05:34
and we need the
05:39
big imager,
05:44
and that's gonna be
05:46
user. I D
05:50
and we need to make sure it's unsigned
05:53
because the big increments that air on all of these tables are unsigned. And to make constraints against data types they have or against columns on different tables. They have to be the same data type.
06:06
So type I d
06:11
again outside
06:16
on. We're gonna need course I d So I'm just gonna copy this and start pasting
06:26
course,
06:27
and then we have the time stamps, so that's good to go
06:31
Now. Next, let's move on to the next table.
06:34
The courses table.
06:38
Andi, this one's got a few things on it.
06:42
So we need a
06:44
gonna pace the big manager from the,
06:46
uh,
06:48
clipboard that have previously. And I'm gonna to change this through room idea.
06:59
I don't need to copy again
07:01
on and
07:03
check this one out table.
07:06
Strange
07:10
course code
07:13
I'm gonna require the course code is unique
07:16
because we can't have duplicates of the course.
07:20
So
07:23
the way that would probably work is that if you had, for example, multiple biology courses, they're the same course, but they would have different course codes,
07:33
and that's how we would keep the same course. We would differentiate between them even though they represent the same course,
07:44
title doesn't have to be unique.
07:47
And some of that just requires knowledge of the domain that you're working in, which you would either understand or someone would make that a requirement for you.
08:00
So
08:03
So we're headed to make the max students and imager,
08:13
and that can't be
08:16
negative. So just make it unsigned, which means positive numbers on Lee, but supports a larger positive number.
08:24
And men students were gonna do the same thing.
08:35
Also going make the title knowable. And what that means is that it doesn't have to be filled in,
08:41
but we're not gonna do the same for the course code. So the course code does have to be filled in,
08:46
and now we need
08:50
Now we need a start date and then date. So I'm gonna do
08:54
table
08:58
time stamp,
09:01
start date. So the date the course starts
09:07
on a table
09:09
time stamped
09:15
and the date
09:18
don't forget my semi colons
09:22
and the grades is representing a relationship to the great symbols who are not going. Teoh put a column for that.
09:28
So we had the created at an up dated at which is created by the time stamps, so we need to delete it. So we're going to table. Soft Deletes.
09:41
Okay, I blew. That finishes that one with you on to the next table, which looks like it's going to be the rooms table
09:50
Onda way. Have a couple thing on a couple things on here.
09:56
So,
09:58
actually, no, we don't want that one. So it's a table
10:03
string
10:07
name.
10:09
You that in the quotes, though, we don't get the syntax correctly. It will, of course, crash.
10:16
So table
10:20
and kind of juror
10:24
Max capacity.
10:28
So again, it can't be negative number.
10:31
That doesn't make sense.
10:33
So I'm just gonna go ahead and make it unsigned. Supports a higher positive number that a regular imager
10:39
and building I d
10:43
wouldn't need that one.
10:46
That needs to be a
10:48
big imager.
10:54
You spell that right?
11:01
Building I d.
11:05
And again, unsigned.
11:09
Okay.
11:13
And we also have the time stamps there for the created at and updated that combs. So we're going and save that. Let's move on to the next table.
11:22
The building table. So we have a couple things on there,
11:28
So table
11:30
string
11:33
name?
11:35
Yeah, let's go ahead and
11:41
address as to City State zip
11:46
names. At one
11:54
address to
11:58
City
12:03
state
12:05
is it
12:07
rooms represents a relationship and the created it created at an updated that will be
12:13
created with the time stamps.
12:16
So it looks like we have everything in here.
12:20
Let's go ahead and run this migration and see if we can't close this lesson down. So
12:24
peace pr. Titian,
12:28
my great fresh.
12:33
Okay, we have a mistake somewhere.
12:37
Let's take a look at the air message and see if we can identify where that is at.
12:41
Okay, so I can very quickly see what the problem is. I spelled soft wrong on one of the previous tables, so I need to go back.
12:52
There's the problem right there.
12:54
And that t And there the correct spelling
12:56
hit up on my keyboard to go to the last command rerun The fresh command.
13:01
Okay, it looks like it's all successful. I'm gonna bring up Dee Beaver. Take a quick peek
13:07
with a refresh
13:11
and let's go ahead and peek into one. Let's do courses.
13:16
Almost like all the columns air there with the right data types.
13:20
Okay, so let's head over to the review and closed the lesson out.
13:26
So that brings us to the summary. And what did we do in this lesson? What we reviewed and updated our e R D diagram, we added a building table to improve our room management for the database architecture. We added column names to the RD diagram so we could see all the column names represented on the tables.
13:43
We added a deleted at column to the course and user to protect against accidental deletion.
13:50
Once we reviewed the RD diagram, we implemented all those columns in our migration files. And then we ran the migration. So that completes this lesson, and I hope to see you in the next Thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor