1 00:00:01,380 --> 00:00:02,213 ‫All right. 2 00:00:02,213 --> 00:00:04,050 ‫So with those tables in place, 3 00:00:04,050 --> 00:00:06,390 ‫now let's create the booking table 4 00:00:06,390 --> 00:00:10,293 ‫and set up the relationships that we talked about earlier. 5 00:00:12,750 --> 00:00:16,983 ‫So new table called bookings. 6 00:00:18,420 --> 00:00:23,420 ‫And then here, let's start with the start date, 7 00:00:23,640 --> 00:00:26,730 ‫and this one should be a timestamp. 8 00:00:26,730 --> 00:00:28,560 ‫So this is going to be a date 9 00:00:28,560 --> 00:00:32,643 ‫and so here in Postgres, those are called timestamp. 10 00:00:34,110 --> 00:00:39,110 ‫So we have a start date and an end date as well. 11 00:00:39,210 --> 00:00:44,210 ‫So when the guest leaves, so again, a time stamp. 12 00:00:47,010 --> 00:00:49,270 ‫Then we also want the number of nights 13 00:00:51,060 --> 00:00:54,730 ‫which in our application will be automatically calculated 14 00:00:55,800 --> 00:00:58,740 ‫but it's still a great idea to store that here 15 00:00:58,740 --> 00:01:00,213 ‫as an integer. 16 00:01:02,400 --> 00:01:07,400 ‫Then the number of guests also an integer. 17 00:01:09,360 --> 00:01:12,960 ‫And then let's store the prices. 18 00:01:12,960 --> 00:01:16,410 ‫So there will be a cabin price 19 00:01:16,410 --> 00:01:20,613 ‫so the price that a guest pays for just a cabin. 20 00:01:21,720 --> 00:01:24,330 ‫So let's make this a float. 21 00:01:24,330 --> 00:01:27,843 ‫And then there will also be some extras price. 22 00:01:28,860 --> 00:01:32,220 ‫So for example, for breakfast and for other stuff 23 00:01:32,220 --> 00:01:36,633 ‫that maybe the hotel might want to add later. 24 00:01:38,070 --> 00:01:41,340 ‫And then finally, there is a total price 25 00:01:41,340 --> 00:01:43,443 ‫which is the sum of the other two. 26 00:01:45,840 --> 00:01:49,383 ‫So then of course it also needs to be a float. 27 00:01:51,390 --> 00:01:53,370 ‫Then here we have the status 28 00:01:53,370 --> 00:01:57,300 ‫which will contain basically the status of the booking. 29 00:01:57,300 --> 00:02:01,380 ‫So whether it is currently checked in, if it is checked out 30 00:02:01,380 --> 00:02:04,560 ‫or maybe the guest hasn't even arrived yet. 31 00:02:04,560 --> 00:02:07,083 ‫So then we will call it unconfirmed. 32 00:02:08,880 --> 00:02:10,413 ‫So let's make this text. 33 00:02:11,460 --> 00:02:14,760 ‫And this really here is the longest one, 34 00:02:14,760 --> 00:02:16,410 ‫so we still need some more. 35 00:02:16,410 --> 00:02:21,410 ‫One of them is whether the user has bought breakfast 36 00:02:23,100 --> 00:02:26,610 ‫for this booking, so has breakfast, 37 00:02:26,610 --> 00:02:28,500 ‫and here we can use the bullion. 38 00:02:28,500 --> 00:02:31,113 ‫So just a true or false. 39 00:02:32,760 --> 00:02:36,870 ‫Then we also want to store whether the user has already 40 00:02:36,870 --> 00:02:41,790 ‫paid this or not, because there might be the possibility 41 00:02:41,790 --> 00:02:44,940 ‫of the guest only paying at the hotel. 42 00:02:44,940 --> 00:02:46,533 ‫So only once they arrive. 43 00:02:48,240 --> 00:02:51,990 ‫And finally, we just want to store some observations 44 00:02:51,990 --> 00:02:54,360 ‫about the booking as well. 45 00:02:54,360 --> 00:02:57,960 ‫So maybe they want to let the hotel know when they arrive 46 00:02:57,960 --> 00:02:59,403 ‫or something like that. 47 00:03:00,660 --> 00:03:04,920 ‫Now, okay so these are all the fields that we need for now. 48 00:03:04,920 --> 00:03:06,630 ‫So the basic fields 49 00:03:06,630 --> 00:03:10,440 ‫but now it is time to actually connect this table 50 00:03:10,440 --> 00:03:13,980 ‫with the guests and with the cabins table 51 00:03:13,980 --> 00:03:17,853 ‫or in other words, to establish relationships between these. 52 00:03:18,690 --> 00:03:21,450 ‫So as we learned two lectures ago 53 00:03:21,450 --> 00:03:25,980 ‫the way we are going to do this is by storing the cabin ID 54 00:03:25,980 --> 00:03:29,133 ‫and the guest ID right here in a booking. 55 00:03:30,480 --> 00:03:32,073 ‫So let's do that. 56 00:03:33,720 --> 00:03:38,720 ‫So cabin ID and now comes the most important part. 57 00:03:38,730 --> 00:03:42,240 ‫So to connect these two, we click here 58 00:03:42,240 --> 00:03:45,030 ‫which actually shows us that this is to edit 59 00:03:45,030 --> 00:03:46,743 ‫the foreign key relation. 60 00:03:47,940 --> 00:03:51,750 ‫And so here is where we do that. 61 00:03:51,750 --> 00:03:55,260 ‫So let's then select a table to reference 62 00:03:55,260 --> 00:03:57,570 ‫which is going to be the cabin. 63 00:03:57,570 --> 00:04:00,060 ‫And the column in there that we want to connect 64 00:04:00,060 --> 00:04:03,270 ‫this with is exactly this ID. 65 00:04:03,270 --> 00:04:06,000 ‫So this is exactly what we had in the diagram 66 00:04:06,000 --> 00:04:10,620 ‫two lectures ago, and this is all we need to do. 67 00:04:10,620 --> 00:04:13,720 ‫So now this table is connected with the ID 68 00:04:14,880 --> 00:04:16,800 ‫or with the cabin ID. 69 00:04:16,800 --> 00:04:20,283 ‫And now let's do the same thing for a guest. 70 00:04:21,270 --> 00:04:26,270 ‫So the guest ID will be a foreign key for the guest's table 71 00:04:27,810 --> 00:04:30,153 ‫and in particular the ID column. 72 00:04:31,950 --> 00:04:36,950 ‫All right, and so now let's then also create a brand new row 73 00:04:37,560 --> 00:04:41,160 ‫in this table to see how the connection between 74 00:04:41,160 --> 00:04:44,673 ‫these tables actually looks like in practice. 75 00:04:53,190 --> 00:04:56,220 ‫So let's add the start date. 76 00:04:56,220 --> 00:04:59,880 ‫And here we can just click on this calendar. 77 00:04:59,880 --> 00:05:02,853 ‫So let's say I want to arrive next Monday, 78 00:05:03,900 --> 00:05:07,470 ‫and I want to leave then on the Friday. 79 00:05:07,470 --> 00:05:10,740 ‫Yeah, so that's going to be just four nights. 80 00:05:10,740 --> 00:05:14,670 ‫So we can write that here with two guests. 81 00:05:14,670 --> 00:05:19,110 ‫And the price here will be 300, let's say. 82 00:05:19,110 --> 00:05:22,500 ‫Then let's say I also want breakfast, so that's going to 83 00:05:22,500 --> 00:05:27,500 ‫be 60 at least if the price of breakfast is 15 84 00:05:27,750 --> 00:05:31,680 ‫as we were saying, and actually that should be 120 85 00:05:31,680 --> 00:05:35,863 ‫because we are two guests, then the total price makes it 420 86 00:05:37,320 --> 00:05:40,383 ‫and the status is unconfirmed. 87 00:05:41,610 --> 00:05:44,670 ‫So I didn't arrive yet at the booking. 88 00:05:44,670 --> 00:05:46,740 ‫So I do have breakfast. 89 00:05:46,740 --> 00:05:49,830 ‫I already paid, let's say 90 00:05:49,830 --> 00:05:54,830 ‫and I will arrive at 10:00 PM so a bit late. 91 00:05:56,700 --> 00:05:59,610 ‫So it's nice to let them know about this. 92 00:05:59,610 --> 00:06:04,470 ‫And now here we can then actually select the cabin. 93 00:06:04,470 --> 00:06:08,760 ‫So let's click here, which will move to the cabin table. 94 00:06:08,760 --> 00:06:11,730 ‫And so here I can now select this one 95 00:06:11,730 --> 00:06:14,220 ‫because it's the only one that exists. 96 00:06:14,220 --> 00:06:17,760 ‫So I'm going to be in cabin number one. 97 00:06:17,760 --> 00:06:22,500 ‫And now I also need to select my own guest information 98 00:06:22,500 --> 00:06:26,010 ‫which again is this one with the ID of one. 99 00:06:26,010 --> 00:06:29,880 ‫So then that's the reason why the one appears there. 100 00:06:29,880 --> 00:06:34,880 ‫And so as I save this, then exactly like we were saying 101 00:06:35,520 --> 00:06:38,580 ‫these important IDs are then saved here. 102 00:06:38,580 --> 00:06:43,470 ‫And we can also see that this is a foreign key relation. 103 00:06:43,470 --> 00:06:48,470 ‫Now, this course is by no means a database or an SKL course 104 00:06:49,350 --> 00:06:54,030 ‫but just as a side note, if in the future you want to model 105 00:06:54,030 --> 00:06:56,730 ‫these kind of relations on your own, 106 00:06:56,730 --> 00:07:00,990 ‫you need to think of these relations in terms of the number 107 00:07:00,990 --> 00:07:03,783 ‫of entities that can be involved. 108 00:07:04,710 --> 00:07:07,440 ‫So that sounds a bit difficult 109 00:07:07,440 --> 00:07:09,810 ‫but let me give you an example. 110 00:07:09,810 --> 00:07:12,900 ‫So here with the bookings and the cabins 111 00:07:12,900 --> 00:07:17,900 ‫each booking can only have exactly one cabin, right? 112 00:07:18,240 --> 00:07:21,720 ‫So one booking can only be about one cabin 113 00:07:21,720 --> 00:07:26,040 ‫not two, not three, but really only one. 114 00:07:26,040 --> 00:07:30,270 ‫On the other hand, one cabin can be involved in many, 115 00:07:30,270 --> 00:07:31,890 ‫many different bookings. 116 00:07:31,890 --> 00:07:36,890 ‫So over time a cabin might be booked, for example, 100 times 117 00:07:37,590 --> 00:07:42,590 ‫but each booking can really only be for one exact cabin. 118 00:07:42,810 --> 00:07:45,690 ‫And so that's what I meant initially when I said 119 00:07:45,690 --> 00:07:50,250 ‫that we need to take into account the number of entities 120 00:07:50,250 --> 00:07:52,890 ‫basically that are involved. 121 00:07:52,890 --> 00:07:54,930 ‫And so this is important 122 00:07:54,930 --> 00:07:58,650 ‫because we need to place the foreign key in the table 123 00:07:58,650 --> 00:08:02,550 ‫that can only reference exactly one other row 124 00:08:02,550 --> 00:08:03,870 ‫from another table. 125 00:08:03,870 --> 00:08:05,970 ‫And so that's why the cabin ID 126 00:08:05,970 --> 00:08:09,150 ‫and the guest ID are right here. 127 00:08:09,150 --> 00:08:13,050 ‫So, because again, each booking can only be about 128 00:08:13,050 --> 00:08:16,983 ‫one exact cabin and one exact guest. 129 00:08:17,940 --> 00:08:18,773 ‫All right. 130 00:08:19,860 --> 00:08:24,630 ‫But anyway, with this, we finished building our tables here 131 00:08:24,630 --> 00:08:26,493 ‫and so let's just quickly move on.