1

00:00:01,080 --> 00:00:05,700

So let's set up the rest of our foreign keys, and we've already done the ones for the reservations



2

00:00:05,700 --> 00:00:10,980

table that links room ID in the reservations table to ID in the rooms table.



3

00:00:11,490 --> 00:00:17,130

And it looks like we have two more, both on the room restrictions, table one that links again, room



4

00:00:17,130 --> 00:00:24,420

ID in this table to the ID column in the rooms table and one that links restriction ID to the restrictions



5

00:00:24,420 --> 00:00:30,140

table column ID so we can do both of those foreign keys in one migration.



6

00:00:30,150 --> 00:00:30,800

So let's do that.



7

00:00:31,380 --> 00:00:34,110

Go back to our idy open a terminal window.



8

00:00:34,230 --> 00:00:45,840

So to generate fees and I'll call this create F and the table is called room restrictions for room restrictions



9

00:00:48,030 --> 00:00:51,240

and that should generate both of our up and down migrations.



10

00:00:51,240 --> 00:00:51,900

And there they are.



11

00:00:51,930 --> 00:00:53,390

So let's do the up one first.



12

00:00:54,090 --> 00:00:59,250

So what I'll do is just take this foreign key that were created in the last lecture and paste it over



13

00:00:59,250 --> 00:01:00,210

here twice.



14

00:01:00,870 --> 00:01:03,870

And the table is room restrictions.



15

00:01:04,500 --> 00:01:11,250

So I'll copy that, put it in the second one and the first one is room ID to rooms caller ID.



16

00:01:11,250 --> 00:01:11,990

So that's OK.



17

00:01:12,450 --> 00:01:20,430

And the second one is for restriction need in the table restrictions column ID.



18

00:01:21,030 --> 00:01:22,050

So that should be fine.



19

00:01:22,260 --> 00:01:23,700

Let's run that migration.



20

00:01:24,900 --> 00:01:26,250

So to migrate.



21

00:01:27,360 --> 00:01:29,780

No they're OK.



22

00:01:30,300 --> 00:01:32,220

And now let's do our down migrations.



23

00:01:32,220 --> 00:01:39,030

So again I'll copy the down migration we did last time and paste it in the down migration's for here



24

00:01:39,150 --> 00:01:41,400

twice and then just modify.



25

00:01:42,030 --> 00:01:45,930

So the table is again room restrictions.



26

00:01:47,970 --> 00:01:55,650

And we need to get the name of the foreign keys, so I'll go over to divert and refresh the foreign



27

00:01:55,650 --> 00:01:57,650

keys on the room restrictions table.



28

00:01:58,650 --> 00:02:04,440

And there are two room restrictions, restrictions, ID and room restrictions, rooms, ID.



29

00:02:05,010 --> 00:02:11,640

So room restrictions, restriction, I.D..



30

00:02:11,670 --> 00:02:12,450

I think that's right.



31

00:02:13,080 --> 00:02:15,150

Room restriction restrictions, plural.



32

00:02:17,010 --> 00:02:24,480

And then instead of room restrictions, restrictions, it's room restrictions, rooms, and that should



33

00:02:24,480 --> 00:02:24,830

work.



34

00:02:24,840 --> 00:02:28,990

So let's try running that just to make sure this is going to work the way we want to sort of migrate



35

00:02:29,040 --> 00:02:29,580

down.



36

00:02:30,540 --> 00:02:31,950

And it gave me an air room.



37

00:02:31,950 --> 00:02:35,640

Restrictions, restrictions, ID of room restrictions does not exist.



38

00:02:35,640 --> 00:02:39,720

So I got the name wrong room restrictions, restrictions.



39

00:02:39,720 --> 00:02:43,800

ID underscore, of course, underscore Kerry.



40

00:02:46,880 --> 00:02:47,510

Underscore.



41

00:02:47,620 --> 00:02:52,200

OK, let's try that again so we'll clear the screen and sort of migrate down.



42

00:02:53,270 --> 00:02:57,770

And again, line two expected next token to be apparently I can't type today.



43

00:02:58,520 --> 00:03:08,170

So line to room restrictions, room restrictions, rooms, I'd try that one more time and hopefully



44

00:03:08,180 --> 00:03:09,480

it didn't run the first down.



45

00:03:10,010 --> 00:03:10,670

That's better.



46

00:03:11,690 --> 00:03:18,150

And now if we go look at this and refresh our foreign keys, there they are.



47

00:03:18,200 --> 00:03:25,400

And if we run the migration again, so to migrate now, they should be here when I refresh this.



48

00:03:27,410 --> 00:03:28,020

Another.



49

00:03:28,460 --> 00:03:33,090

So there's all of our foreign key setup, is that everything we need to do?



50

00:03:33,110 --> 00:03:34,190

Well, no, it's not.



51

00:03:34,220 --> 00:03:35,810

There are a few other things we want to do.



52

00:03:35,810 --> 00:03:41,690

For example, in the user's table, we have this column ID and that's what people are going to use to



53

00:03:41,690 --> 00:03:42,160

log in.



54

00:03:42,170 --> 00:03:44,470

They're going to type in their email address and a password.



55

00:03:44,990 --> 00:03:51,460

And what we want to make sure is not possible is that we can have two users with the same email address.



56

00:03:51,740 --> 00:03:56,390

So we want to add a constraint on this table that makes the email column unique.



57

00:03:56,600 --> 00:03:57,890

So we'll do that for sure.



58

00:03:58,550 --> 00:04:03,440

The second thing is sometimes you're going to have a lot of data in a table.



59

00:04:04,040 --> 00:04:08,750

And, for example, this reservation table is going to have a lot of reservations over time.



60

00:04:08,930 --> 00:04:13,310

The room restrictions table is going to have a great number of restrictions over time.



61

00:04:13,730 --> 00:04:19,130

If this site's up and running for, say, two years, there might be 100000 entries in the room restrictions



62

00:04:19,130 --> 00:04:24,950

table indicating when reservations are blocking dates off, when the property owner has decided to block



63

00:04:24,950 --> 00:04:26,650

a given date or range of dates off.



64

00:04:26,690 --> 00:04:29,450

Whatever the case may be, there's going to be a lot of data in there.



65

00:04:30,410 --> 00:04:36,590

And if we don't do something to speed up access to that table, it will be very, very slow to read



66

00:04:36,590 --> 00:04:36,830

through.



67

00:04:36,830 --> 00:04:43,700

It will wind up doing is what's called a road level table scam, where in order to find, for example,



68

00:04:43,910 --> 00:04:50,600

all of the restrictions for a given room, the database will actually scan through the entire table



69

00:04:50,600 --> 00:04:55,370

looking for things that have a given remedy and that's not efficient at all.



70

00:04:55,380 --> 00:04:59,350

And we can speed that up a lot by putting an index on a given field.



71

00:04:59,630 --> 00:05:05,630

So, for example, if I put an index on the remedy that the database will actually store a method of



72

00:05:05,630 --> 00:05:11,680

accessing information about the room restrictions table for a given remedy very, very quickly.



73

00:05:12,110 --> 00:05:16,850

So we're going to want to add one or more indices on the tables.



74

00:05:17,180 --> 00:05:19,370

So we'll take care of that in the next couple of lectures.