1 00:00:01,400 --> 00:00:07,790 So we've looked at databases and we've looked at how to create tables, how to insert data, update 2 00:00:07,790 --> 00:00:13,640 data, delete data, select data, that sort of thing, but so far we've just worked with test tables 3 00:00:13,640 --> 00:00:17,280 and none of them are really useful for our application. 4 00:00:18,020 --> 00:00:23,030 So what I want to do now is actually to outline the structure of our database. 5 00:00:23,030 --> 00:00:25,820 And normally when I do this, I use pen and paper. 6 00:00:25,850 --> 00:00:32,300 I'm kind of old school that way, but I can't figure out an easy way to record myself with a pen and 7 00:00:32,300 --> 00:00:34,930 paper that will actually work for these videos. 8 00:00:34,970 --> 00:00:39,680 So instead, I'm using a tool, a tool called Siecle Ed, and it's a commercial product. 9 00:00:40,220 --> 00:00:42,770 I only bought it for this course. 10 00:00:43,100 --> 00:00:44,570 I normally don't do it. 11 00:00:44,900 --> 00:00:47,450 Don't do my schemas using a piece of software. 12 00:00:47,450 --> 00:00:48,530 I just use pen and paper. 13 00:00:49,010 --> 00:00:54,890 But this will be enough to actually show you how I go about determining what the structure of my database 14 00:00:54,890 --> 00:00:55,520 is going to be. 15 00:00:56,180 --> 00:01:02,180 So don't pay any attention to the actual tool itself or to any errors it might display, because I'm 16 00:01:02,180 --> 00:01:07,370 just using this to show how you might work on the schema for your database. 17 00:01:07,700 --> 00:01:09,940 So let's think about what we actually need. 18 00:01:10,040 --> 00:01:15,710 First of all, I'm going to add a table and I'm going to add a table that I'm going to call users. 19 00:01:16,550 --> 00:01:22,030 So I'll give the table a name users and notice I do use plural when I'm talking for most. 20 00:01:22,070 --> 00:01:27,980 Most of my tables will have the plural word for their name and you'll see why when we get to a developing 21 00:01:27,980 --> 00:01:28,400 database. 22 00:01:28,400 --> 00:01:33,980 Models now, this table is going to have to have several rows, of course. 23 00:01:33,990 --> 00:01:35,270 So let's add a field to it. 24 00:01:35,930 --> 00:01:39,670 And the first one will be an ID and I'll call that an intelligent. 25 00:01:39,680 --> 00:01:46,640 It'll actually be a serial in our final database structure and auto incrementing field and it will be 26 00:01:46,640 --> 00:01:48,710 an unsigned integer of length 11. 27 00:01:48,800 --> 00:01:52,030 So that will be the ID that I can find a given user for. 28 00:01:52,040 --> 00:01:57,110 And when I talk about users here, I'm not talking about people who are making reservations. 29 00:01:57,110 --> 00:02:00,700 I'm talking about the people who will log into the back end. 30 00:02:00,710 --> 00:02:06,500 We're going to build later in this course to review reservations, to modify them, delete them, whatever 31 00:02:06,500 --> 00:02:07,210 the case may be. 32 00:02:07,220 --> 00:02:13,130 In other words, the property owner in the case of our application and I need some information about 33 00:02:13,130 --> 00:02:14,330 those users. 34 00:02:14,330 --> 00:02:21,170 So I'm going to need another field here and I'll call that field first name and I'll make it a vaka 35 00:02:22,040 --> 00:02:22,790 255. 36 00:02:22,950 --> 00:02:24,370 That's long enough to hold a first name. 37 00:02:24,860 --> 00:02:26,150 Let's give them a last name. 38 00:02:28,130 --> 00:02:32,270 And it also will be of very character, varying of our car, 255. 39 00:02:33,020 --> 00:02:35,960 And then I need something to allow those users to log in. 40 00:02:36,470 --> 00:02:40,640 So what is a piece of information that's going to be unique to a given user? 41 00:02:40,650 --> 00:02:43,950 No two users will have this same piece of information. 42 00:02:44,480 --> 00:02:46,970 Well, the easiest thing, of course, is an email address. 43 00:02:47,090 --> 00:02:53,060 So let's add an email address, which I'll just call email and I'll make it vaka and I'll make it 255 44 00:02:53,060 --> 00:02:53,420 again. 45 00:02:54,890 --> 00:02:56,370 And then they have to have a password. 46 00:02:56,480 --> 00:03:00,430 Now, we are not going to store passwords in the database at all. 47 00:03:00,440 --> 00:03:02,000 That is a terrible idea. 48 00:03:02,300 --> 00:03:08,150 And if you've been paying attention to the news at all for the last decade or so, invariably every 49 00:03:08,150 --> 00:03:16,190 year someone's database is compromised by an attacker and passwords are released out into the wild. 50 00:03:16,190 --> 00:03:16,870 And that's bad. 51 00:03:16,910 --> 00:03:21,080 We're going to store instead a hash of the password, but I'm going to create another field and I will 52 00:03:21,080 --> 00:03:24,500 call that field password, even though it's going to hold a password hash. 53 00:03:24,860 --> 00:03:26,120 And I'll make that right now. 54 00:03:26,120 --> 00:03:27,800 I'll make it a Barpak 255. 55 00:03:27,830 --> 00:03:32,210 That'll probably change, but I'm just outlining the structure of our database. 56 00:03:32,420 --> 00:03:35,590 OK, so a 255 character password. 57 00:03:35,720 --> 00:03:38,000 Is there anything else I should store here? 58 00:03:38,540 --> 00:03:45,050 Well, it's always a good idea to keep track of when a particular piece of information in a database 59 00:03:45,410 --> 00:03:48,210 was created and when it was last updated. 60 00:03:48,560 --> 00:03:55,580 So I will create two new fields created at which I'll make a timestamp and that will store the date 61 00:03:55,580 --> 00:03:58,310 and time that this particular row was created. 62 00:03:58,790 --> 00:04:05,120 And I'll add one more file called Updated App, and I'll make that again, a timestamp. 63 00:04:08,090 --> 00:04:16,370 And the last thing I'm going to stick in here is an access level, because there may be situations where 64 00:04:16,370 --> 00:04:22,010 I want different users of the back end to have different permissions and an easy way to do that. 65 00:04:22,010 --> 00:04:24,710 It's just to determine what their access level might be. 66 00:04:24,710 --> 00:04:26,230 And I'll just make that for now. 67 00:04:26,390 --> 00:04:31,340 So I'll call it access level and it'll be an integer size 11. 68 00:04:32,060 --> 00:04:32,440 All right. 69 00:04:32,450 --> 00:04:38,420 So, for example, someone with an access level of three might be a super user and they can do everything. 70 00:04:38,420 --> 00:04:43,610 Someone with an access level of two can do some things, but not everything that a super user can do, 71 00:04:43,610 --> 00:04:44,540 so on and so forth. 72 00:04:44,550 --> 00:04:49,010 So I'm not sure what those actually level access levels are going to be right now, but I know they're 73 00:04:49,010 --> 00:04:49,820 going to exist. 74 00:04:51,230 --> 00:04:54,310 So that's probably enough for our user table right now. 75 00:04:54,320 --> 00:04:56,300 So I'll stick that over here just to get it out of the way. 76 00:04:57,020 --> 00:05:00,470 Now, what other kinds of information are we going to need to keep track of? 77 00:05:00,600 --> 00:05:06,090 Well, obviously reservations, so let's create a new table and I'll drag it over here just so we can 78 00:05:06,090 --> 00:05:08,250 put everything on the same screen at the same time. 79 00:05:08,790 --> 00:05:15,990 And I will call this reservations, plural, risc our Vatican reservations that will have a number of 80 00:05:15,990 --> 00:05:16,440 fields. 81 00:05:16,450 --> 00:05:21,000 Obviously it's going to have an ID field, which I'll make it into LeBon unsigned so you can't have 82 00:05:21,000 --> 00:05:22,100 negative numbers in there. 83 00:05:23,520 --> 00:05:26,670 And what kind of information are we storing for a given reservation? 84 00:05:26,700 --> 00:05:31,320 Well, there's the information we have on our form that we created a while ago, but there's actually 85 00:05:31,320 --> 00:05:32,640 more information we're going to need. 86 00:05:32,670 --> 00:05:33,990 Let's do some obvious things. 87 00:05:34,680 --> 00:05:39,840 The guest will have a first name and I'll make that of our car to 55. 88 00:05:41,130 --> 00:05:42,510 The guest will have a last name. 89 00:05:45,060 --> 00:05:46,560 And again, character varying. 90 00:05:48,760 --> 00:05:55,330 255 is sufficient for that, they're going to have an email address, which I'll call email and make 91 00:05:55,330 --> 00:05:57,330 that a character very 255. 92 00:05:58,660 --> 00:06:02,470 They're going to have a phone number, which I'll just call phone. 93 00:06:02,870 --> 00:06:05,300 And again, I'll make it a character bearing 235. 94 00:06:06,940 --> 00:06:10,750 We also need to know what room they're going to stay in and how are we going to do that? 95 00:06:10,870 --> 00:06:11,730 Well, I'm not sure. 96 00:06:11,740 --> 00:06:13,410 Let's get back to that in a minute, OK? 97 00:06:13,780 --> 00:06:16,740 Well, they're definitely going to have a start date and an end date for their stay. 98 00:06:16,810 --> 00:06:23,470 So we'll say start date, which will just be a date and they'll have an end date. 99 00:06:29,310 --> 00:06:30,360 Which will also be a date. 100 00:06:32,820 --> 00:06:37,900 And we also need the room they're going to stay in and I'm not sure how I'm going to do that. 101 00:06:37,920 --> 00:06:43,170 Well, let's just create another table right now, and I'll put it right beside this one and I'll call 102 00:06:43,170 --> 00:06:44,940 it rooms. 103 00:06:48,690 --> 00:06:52,720 And this table will hold information about all the various rooms that we have. 104 00:06:52,740 --> 00:06:59,490 So let's add field and the first one, of course, will be it won't make it into 11 unsigned and a room 105 00:06:59,490 --> 00:07:05,390 name, which I'll just call room names and I'll make that a character very 255. 106 00:07:06,690 --> 00:07:08,000 And that might be enough. 107 00:07:08,010 --> 00:07:12,540 I probably could store price and things like that in there, but I'm just going to leave those like 108 00:07:12,540 --> 00:07:13,500 they are for right now. 109 00:07:13,500 --> 00:07:15,950 And we can modify this table later if we need to. 110 00:07:16,680 --> 00:07:22,100 And now I need a link between this table reservations and this table rooms. 111 00:07:22,260 --> 00:07:28,170 So how can I determine which reservation or which room a given reservation is tied to? 112 00:07:28,200 --> 00:07:34,860 Well, I can do that by adding a field here and I'll call it room ID and it will be of type into 11 113 00:07:34,980 --> 00:07:35,700 unsigned. 114 00:07:36,840 --> 00:07:43,290 And then I'll put a link between this room idy and whatever ID the room they're staying in happens to 115 00:07:43,290 --> 00:07:43,610 be. 116 00:07:43,620 --> 00:07:48,330 And in this particular tool I can do it by dragging a line from here to here. 117 00:07:49,260 --> 00:07:57,690 And now we have what's called a foreign key relationship between the field ID and the field ID in the 118 00:07:57,690 --> 00:08:01,580 table rooms and foreign keys actually are pretty cool. 119 00:08:02,460 --> 00:08:09,360 They will prevent you from accidentally entering a value in this table that doesn't exist in this table. 120 00:08:09,360 --> 00:08:14,130 We can put constraints on foreign key relationships and we'll do that when we actually build these tables, 121 00:08:14,130 --> 00:08:16,010 which will do very soon. 122 00:08:16,320 --> 00:08:22,350 But for right now, just bear in mind that the link between where a given reservation is, what room 123 00:08:22,350 --> 00:08:30,690 it's for, and the rooms table is done by having a link between this column remedy in the table reservations 124 00:08:30,900 --> 00:08:35,690 and this column rooms in the rooms table the ID column in the room table. 125 00:08:35,730 --> 00:08:40,590 So there's a foreign key relationship and that will be very useful for us that helps us enforce something 126 00:08:40,590 --> 00:08:44,160 called referential integrity in our database structure. 127 00:08:44,460 --> 00:08:51,000 So you can't have if you only have ID one in 82 for rooms, for example, you can't accidentally put 128 00:08:51,000 --> 00:08:55,770 ID zero or ID three in the reservations table in the column room. 129 00:08:56,070 --> 00:08:59,510 So that's a very useful way of enforcing integrity in our database. 130 00:09:00,390 --> 00:09:07,110 So so far, I have a user's table, I have a room table and I have a reservation table. 131 00:09:07,710 --> 00:09:14,460 And one thing that seems to be missing at this point is a means of determining whether or not a given 132 00:09:14,460 --> 00:09:16,800 room is available on a given day. 133 00:09:16,920 --> 00:09:21,450 And we're going to do that by creating another table, which I'll call room restrictions, 134 00:09:24,810 --> 00:09:28,060 and I'll put that right here so we can actually see it a little bit. 135 00:09:28,740 --> 00:09:30,830 Now, this obviously is going to have its own columns. 136 00:09:30,960 --> 00:09:35,190 First one is going to have is the same one we've been using right along called ID and I'll make that 137 00:09:35,190 --> 00:09:36,660 into level unsigned. 138 00:09:38,880 --> 00:09:43,860 And then we're going to store in this table several pieces of information. 139 00:09:43,950 --> 00:09:50,430 First of all, let's put in two dates, start date, which will be of type date 140 00:09:53,820 --> 00:09:54,510 and end date. 141 00:09:57,870 --> 00:10:04,070 Which will also be of tight date, and then we'll add another one room remedy. 142 00:10:06,800 --> 00:10:16,730 Which will be of type int 11, unsigned and finally reservation ID, which will also be of type int 143 00:10:17,210 --> 00:10:24,330 eleven unsigned, and then I'll put in those two fields that I said we really should keep track of and 144 00:10:24,380 --> 00:10:27,710 field created at, which is of type timestamp 145 00:10:30,140 --> 00:10:31,130 updated at, 146 00:10:34,190 --> 00:10:35,750 which is a type timestamp. 147 00:10:38,120 --> 00:10:39,710 And that's probably enough for right now. 148 00:10:39,800 --> 00:10:47,360 As you've probably guessed, the room ID is also a foreign key to the column ID in the table rooms, 149 00:10:47,810 --> 00:10:54,810 and in this case, reservation ID is a foreign key to the column ID in reservations. 150 00:10:55,430 --> 00:11:01,910 So now I probably should put, created and updated as in here to let's do it before I forget. 151 00:11:04,310 --> 00:11:16,070 So Attfield created at which will be a timestamp and Arrowfield updated at which would be a timestamp 152 00:11:17,180 --> 00:11:29,210 and the same thing over here and field created at time and updated us which will be a timestamp. 153 00:11:29,990 --> 00:11:30,400 All right. 154 00:11:31,700 --> 00:11:34,760 So now I have four tables. 155 00:11:36,920 --> 00:11:41,660 Does this cover all of the information we're probably going to need to store in our database? 156 00:11:41,690 --> 00:11:42,530 Well, no, not quite. 157 00:11:42,930 --> 00:11:44,100 So let's think this through. 158 00:11:44,750 --> 00:11:51,110 Is there any situation where a room might not be available even though there's not a reservation? 159 00:11:51,230 --> 00:11:55,700 And in fact, there is suppose there's being some maintenance is being done on that room? 160 00:11:55,910 --> 00:12:01,370 Or suppose the property owner wants to go away for two weeks in December and not rent anything during 161 00:12:01,370 --> 00:12:04,030 the two weeks that they're away because there'll be no staff on hand. 162 00:12:04,640 --> 00:12:10,070 So what we can do is add another piece of information to our room restrictions table that determines 163 00:12:10,070 --> 00:12:12,240 what kind of restriction it is. 164 00:12:12,260 --> 00:12:21,170 So I will Arrowfield and I will call it restriction ID and I'll make it into 11 on site and I'll create 165 00:12:21,170 --> 00:12:26,090 one more table called restriction types or just call it restrictions. 166 00:12:26,660 --> 00:12:35,000 That's even better restrictions and it will have an ID field, which will be an unsigned integer size 167 00:12:35,000 --> 00:12:41,900 11, and we'll call the other field restriction name. 168 00:12:47,480 --> 00:12:57,710 It'll be a marker 255 and then our standard created an updated height and field created at time stamp 169 00:12:59,090 --> 00:13:08,150 and add field updated at Timestamp, and then we'll make a foreign key relationship between restriction 170 00:13:08,150 --> 00:13:11,990 ID and the ID column of the restrictions table. 171 00:13:12,860 --> 00:13:13,270 All right. 172 00:13:13,610 --> 00:13:17,690 Now, let me drag these things around a little bit so it's easier to see how everything is related to 173 00:13:17,690 --> 00:13:18,440 everything else. 174 00:13:23,300 --> 00:13:27,720 So here is potentially a workable database schema. 175 00:13:28,340 --> 00:13:33,890 Will this store all of the information we need in order to complete building our application? 176 00:13:35,360 --> 00:13:36,440 Well, it probably will. 177 00:13:36,590 --> 00:13:38,660 And there may be other information I want to store. 178 00:13:38,660 --> 00:13:43,930 Maybe I want to display the price of a room or of a given stay in a given room. 179 00:13:44,060 --> 00:13:49,430 So then I'd have to have a price for a room so I could put a price column right in the rooms table. 180 00:13:49,700 --> 00:13:55,820 Or I could create another table called prices and allow different prices for different times of the 181 00:13:55,820 --> 00:13:56,110 year. 182 00:13:56,510 --> 00:14:02,150 But for our purposes, in learning how to build a Web application, this is probably sufficient for 183 00:14:02,150 --> 00:14:02,710 right now. 184 00:14:03,080 --> 00:14:08,480 So we wind up having a users table that will allow our property owner to authenticate and log into a 185 00:14:08,480 --> 00:14:09,100 back end. 186 00:14:09,680 --> 00:14:16,940 We have a means of storing reservations and storing rooms and determining whether or not a room is available. 187 00:14:16,980 --> 00:14:18,830 Now, let's talk about this one a little bit more. 188 00:14:18,830 --> 00:14:22,550 The room restrictions, what purpose does this table serve? 189 00:14:23,420 --> 00:14:28,790 Well, imagine, if you will, that both rooms are completely available right now for every day in the 190 00:14:28,790 --> 00:14:29,270 future. 191 00:14:29,480 --> 00:14:35,090 And then someone comes to our Web site and they make a reservation, say, for June the 1st to June 192 00:14:35,090 --> 00:14:40,670 the 3rd, 2012 in the generals quarters, not 2012. 193 00:14:40,670 --> 00:14:41,780 2012 is in the past. 194 00:14:41,780 --> 00:14:43,360 Time travel hasn't been invented yet. 195 00:14:43,370 --> 00:14:49,010 Let's try that again, June 1st to June the 3rd, 2022, which is in the future right now as I record 196 00:14:49,010 --> 00:14:49,270 this. 197 00:14:50,390 --> 00:14:57,620 So when they do that, we save the reservation information and we insert a row in the room restrictions 198 00:14:57,620 --> 00:15:04,670 table that says hey for room Itaewon one, which might be the generals quarters from June the 1st to 199 00:15:04,670 --> 00:15:12,500 June the 3rd of 2022, we're going to put in a restriction for this room tied to this reservation instead 200 00:15:12,980 --> 00:15:16,910 of type whatever type we've determined the restriction is for a booking. 201 00:15:17,780 --> 00:15:18,950 We just enter a row in there. 202 00:15:18,980 --> 00:15:24,800 So now we have one row and our room restrictions table that says the room is booked from this date to 203 00:15:24,800 --> 00:15:25,340 this date. 204 00:15:25,970 --> 00:15:33,170 The next time we check to see if a room is available, we fire a query off to this table saying, hey, 205 00:15:33,170 --> 00:15:41,030 someone wants to stay from this date, to this date, in this room, do you have any rows in the room 206 00:15:41,030 --> 00:15:48,440 restriction room restrictions table where the dates that I want are overlapped by any of the restrictions, 207 00:15:48,440 --> 00:15:51,470 any of the rows in your database for this given room? 208 00:15:52,100 --> 00:15:58,460 So if I get a non-zero response, I get yes, I have two rows that are overlapping the dates that you 209 00:15:58,460 --> 00:16:01,070 want, then I know the room is not available for those dates. 210 00:16:01,400 --> 00:16:06,740 But if I get zero, no, there are no rows that that overlap the date you're requesting for this particular 211 00:16:06,740 --> 00:16:08,660 room, then I know the room is available. 212 00:16:08,870 --> 00:16:12,080 That's the only purpose that the room restrictions table service. 213 00:16:12,260 --> 00:16:17,780 It lets us query the database to find out whether or not a given room is available. 214 00:16:18,230 --> 00:16:22,520 So when we actually write the query to do that search, this will become very, very clear. 215 00:16:22,790 --> 00:16:25,360 But hopefully you understand the purpose that this table serves. 216 00:16:25,940 --> 00:16:29,690 So right now I have a workable database schema. 217 00:16:29,690 --> 00:16:32,450 So what we need to do is to go and create these tables. 218 00:16:33,110 --> 00:16:39,950 Now, what I'm not going to force us to do is to go and build these tables by hand using Dvor or any 219 00:16:39,950 --> 00:16:40,760 database tool. 220 00:16:40,970 --> 00:16:46,580 Instead, we're going to use database migration's we're going to take advantage of a tool where we can 221 00:16:46,580 --> 00:16:54,980 describe our our tables using a simple go syntax and then run a tool that will create those tables for 222 00:16:54,980 --> 00:16:55,280 us. 223 00:16:55,280 --> 00:17:01,190 And the advantage of this is that we have stored in our git repository and as part of our application, 224 00:17:01,410 --> 00:17:05,510 a means of recreating our database structure any time we want to. 225 00:17:06,020 --> 00:17:12,860 And as our database structure changes over time and it will, we can simply write new migrations that 226 00:17:12,860 --> 00:17:19,280 allow us to apply our changes to our database and ensure that we never have to go and create a database 227 00:17:19,280 --> 00:17:20,000 manually. 228 00:17:20,180 --> 00:17:28,460 We can instead describe it programmatically, encode and run migrations up to apply them or down to 229 00:17:28,460 --> 00:17:29,150 reverse them. 230 00:17:29,150 --> 00:17:35,450 And this is a really good way of ensuring that your database will always be under control and will never 231 00:17:35,450 --> 00:17:39,590 just kind of grow organically and and fall out of sync with your code base. 232 00:17:39,890 --> 00:17:42,590 So we'll take care of that in the next few lecture's.