Current section: Data Relationships 4 exercises

Intro to Data Relationships


00:00 How's your relationship? I hope it's awesome. We're gonna talk about database relationships here. So we're gonna do three of the most common database relationships, one-to-one, one-to-many, and many-to-many. And so I wanna show you a couple of examples before we get started,

00:16 and specifically in Prisma schema language. So here we have a person, a single person can have a single social security number here in the US, and that social security number is gonna be separate. Now, a common question with, why do we do one-to-one? Why can't we just like take these and stick them up in here?

00:36 And then we, of course, change this to social ID or something like that, right? And then we don't need to establish this relationship at all. And in fact, we wouldn't even need the ID. We could just say SSN, boom, that's it. Like that would be a lot easier. Kent, why don't we just do that? There are a couple of reasons that you might wanna do this.

00:55 In particular, when users or developers are selecting from the database, they might say select star, and then they would get all of the data from the database, including the social security number. Not something that you'd really want. Whereas the default here is the person actually doesn't have any data in the database about the social security number.

01:16 That relationship is completely managed by the social security number model and that table via the person ID. That's how that relationship is gonna be established. And so by separating this one-to-one relationship out into two separate tables, it protects you from accidentally selecting stuff

01:35 from one table. The other thing is, let's say that you had a couple of other fields that were also required if that social security number exists. So for example, let's say like a brand new newborn baby is definitely a person and may have a name that parents may call them, but they don't have a social security number yet. And so this is optional.

01:54 But let's say that we have a field here called stolen. That's a Boolean. That would just be like a silly thing or something like that. If that were the case, then we need to have some mechanism to say that the number is required and the stolen property is required.

02:11 If one of them exists, they both have to exist. And you can't do that at the database level, but you can say that if this model exists, like both of those are required fields of the model. And so you can say this is an optional field on the person, but then within that model,

02:30 you can define the fields within that model that are optional or required, and they can all be required together. So there are various reasons why one-to-one relationships are really useful. And we're gonna have that in our exercise. The next one is a one-to-many. So here a person can have multiple phone numbers. It's kind of confusing, but they totally can.

02:50 They can have their work phone. They can have their, back in my day, we had landlines, and so you'd have your home phone. And then during that transition, I also got a cell phone. And so you had a home phone, cell phone, work phone. It's just too many phones. But the fact is people can have multiple phones.

03:08 But a single phone number only belongs to a single person. We'll say it's the head of household or whoever's paying the phone bill is the person who owns or who that phone number belongs to. So this is the one-to-many relationship where that, again, the relationship is established

03:26 and controlled by the ownee, the one that is, yeah, the ownee, the one that's owned. So the phone number is owned by the person. And then the relationship is, or you define the property of the relationship

03:45 on the person model. But this doesn't actually end up resulting in any data in the database on the person model. So that's what the syntax looks like for that. We'll dive into this relationship attribute in this exercise a little further. And then you have a many-to-many.

04:02 So this is, we actually don't have a part of the exercise or a part of the app where it actually makes sense to have a many-to-many. But a really common example of this is to have post and tag. So let's say you're writing a blog about how great OneWheels are.

04:20 And so you'd have different tags for different locations that you're going to on your OneWheel, or different terrain that you ride, or now this is a blog post about accessories. And so there are different tags that are associated to blog posts. And a tag can be associated to multiple blog posts, and multiple blog posts can have the same tag.

04:40 So that's a many-to-many relationship. In that world, you actually cannot accurately describe that relationship using just the two tables, where you have the post table and the tag table. You actually need a third table that establishes that relationship. In Prisma land, you actually don't need to worry about that

05:03 because Prisma will create that relationship for you. And all you, or that table for you, all you need to do is define what property you want to access the tags on from the post, and specify this is an array of, or a collection of these tag models. And then on the tag, we say we want to access posts

05:22 via the post property, and that's gonna be a collection of posts. And then what Prisma will do behind the scenes is it creates a third table that has rows where it defines the ID to the post and the ID to the tag, and here's that relationship there. And so Prisma actually makes this type of relationship pretty easy to work with,

05:42 even though it's a little bit more complicated in a normalized database like SQLite. So that is what we're gonna be doing in this exercise. It's establishing some of these relationships for our images, and it's gonna be great. I think you're gonna have a fun time,

06:00 so yeah, let's get at it. I said our images, we're also gonna be working with our notes, so a user can own multiple notes. So we're gonna establish a couple of relationships in this exercise. So have a good time, and we'll see you on the other side.