The Booking Table

[00:00 - 00:12] We're almost ready to get to the central part of our data model, which is the booking table. We'll be using the TS range type that we played around with in an earlier lesson.

[00:13 - 00:26] But before we do that, let me just quickly add to other tables, which is the customer table that is for now identical to the provider table. And then a service type table that will just allow people to offer different kinds of services.

[00:27 - 00:40] So these are very small utility tables. I'm just going to speed things up a little bit here so that we don't spend all of our time looking into this. So I'll add some things to, well, actually, let me just roll back first.

[00:41 - 00:53] Migrate rollback. And then we will add a little bit to this migration. So a customer table that looks exactly like the provider table for now.

[00:54 - 01:00] And then a service type table that also has an ID. And then it just has a name and a description.

[01:01 - 01:08] So this is basically so that a provider can offer different types of services. It's just a little thing we'll be using in our UI.

[01:09 - 01:23] We now want to create the booking table and we're going to use the B3 just extension that we used in our when we were playing around with it. And well, let me just copy this really quickly.

[01:24 - 01:25] And then we will go. All right.

[01:26 - 01:41] So we add the extension as we did before and then we create this table booking. And it's basically what we were doing before when we created the appointment table and playing around except now we have a customer ID and a provider ID as well.

[01:42 - 01:59] The time range is called during and we're still using the exclusion constraint. But now we say that the provider ID has to be included in this constraint so that for the same provider, no two overlapping appointments can be created.

[02:00 - 02:12] Obviously, you should be able to create an appointment with a different provider in a time, a slot that has already been used by an initial provider. We then create a little comment on the booking table.

[02:13 - 02:27] This is optional, but I like to do this because it's in the database and this will be taken into account by panel and added as a comment on the TypeScript type as well. And it can be just really nice to have that handy when you hover over something .

[02:28 - 02:52] We also add a comment to the during column to make sure that people realize that this is where the booking is actually stored. And then we add two indices to make it quick to look up one of these either by customer or by provider because we will definitely be needing that. With all this in place, let me just create the equivalent down migration here.

[02:53 - 03:09] I'm just going to copy this like so. So now we drop all of the things that we're creating in the reverse order as we need to.

[03:10 - 03:23] Let's try and run this and see if it satisfies our rules. It does. Let's try and run the panel.

[03:24 - 03:34] There we go. We now have some new entries here. So customer is pretty straightforward. There should be no surprises here.

[03:35 - 03:58] At this point, you might start to realize why the branding is valuable. So customer ID is a number as is provider ID, but it's impossible to assign a customer ID to a provider ID of vice versa. So this is now protecting us from accidentally mixing up those two, which might be something we might otherwise end up doing by accident.

[03:59 - 04:17] We have the service type table. There is nothing specific or particularly interesting about this. And then we have our booking table here. And you can see that first of all, cannel has added this comment that we created as well as the time of booking comment on the column.

[04:18 - 04:28] It sets the during to a string. And that is the default behavior that cannel will do for ranges. And that is because they have the tuple format that you might remember.

[04:29 - 04:45] So in order to not lose any information, default for any range is to create a string. We're going to be customizing this a little bit later on, but for now string works just fine.