Current section: Data Relationships 4 exercises

Database Design


00:00 We've got to get these images in here now. And now we're actually going to add images to users as well. So nodes have images. Users have images. When I originally put this together, I thought, you know, there's a lot of different kinds of files that you could potentially create in an application.

00:16 You've got PDFs and CSVs and images, of course, and all sorts of different types. So why don't we do a file type that's just a generic thing where you stick a blob of stuff. And then we can have an image type of that that depends on the file, so a one-to-one relationship between these two.

00:34 And then the user can have that relationship. This should be a one-to-one relationship that I'm representing that without arrows. And so we can just have it that way for the user, and then the image can point to a note. But by doing things this way, we have two issues.

00:53 First of all, the image is going to have an optional user ID and an optional note ID because an image can't be assigned to both a user and a note. And we can't, like, we wouldn't enforce that either, even if that were possible. And so, yeah, this isn't awesome because when you look up an image,

01:12 you're not sure, like, is the user there or is the note there? Now you have to do all this checking. Not very fun and not very interesting. And my primary goal here was just to reduce the amount of duplication. And the thing you just have to realize, especially with databases, is the less you have to change things in the future

01:31 as things change, requirements change or whatever, the better. So you want to, like, in regular code, you want to optimize for change as much as possible. In database, it's even more important that you do because migrations are going to inevitably happen and they are a bit more tricky than migrating code

01:51 because now you've got data that you have to migrate over, which is kind of a bit of an annoyance. And so I do not recommend doing something like this. Polymorphism like this, where you just have this base thing and then this extends and extends or inherits or whatever,

02:08 that just is not a great way to fly in a database. So duplication is way better in this world. Yeah, also having it point to multiple things, like this can be a user's image or it can be a note image that just makes things a lot more complicated.

02:26 And so, yeah, what I recommend instead is we just duplicate. We have multiple tables. So we have a user image and we have a note image and they are completely separate. The user image belongs to a user and there's a one-to-one relationship there. So one user can have one image, one image can belong to one user.

02:45 It can be optional. So users don't have to have an image, but if they do, it only belongs to them and they can only have one. And then we have another one-to-many relationship here where a note can have multiple images, but those images can only belong to a single note. That relationship we already did.

03:04 It's a pretty simple relationship. So the biggest problems with this approach is now we've got duplication between the user image and the note image. But as far as the database is concerned, there's no extra, well, I mean, there's a little bit of extra data just to establish the tables and stuff, but the contents of the tables,

03:23 they may have the same columns, but it's not like we're duplicating data or anything. So even though it may feel a bit like, oh, we're duplicating code, like it's really not that much code and it ends up being way, way simpler to work with in day-to-day. So yeah, avoid polymorphism in databases.

03:41 It's just a road to sadness. The other thing that I wanna comment on with what we're doing here is we are storing images in the database. This might take some of you by surprise. You might think, well, why don't we just use S3 or host it with Cloudinary or Cloudflare or something like that.

04:01 And that absolutely is a great option and something that many people do. But I would suggest that when you're getting a new product off the ground, it makes a lot of sense to just put the image in the database, even in SQLite. What's really interesting about SQLite

04:20 is that just the nature of it being a single file, you end up with situations with small files like the ones that we're mostly dealing with here, you can actually have it be faster to serve an image out of SQLite than it can be serving it out of the file system, in some cases.

04:38 This doesn't apply all the time. But the point is that it's probably fast enough for something that you're just getting started using. And even at certain scales, it certainly is going to be fast enough. Now, you get to a certain scale of you're just dumping tons of images or maybe the files that you're putting in here are enormous, then yeah, you'll get to a point

04:58 where this is definitely not a recommended approach and you'd either use a service or have a persistent file system. And in the future, that would potentially make sense. But I think that storing images in a database right from the get-go is a great place to put it

05:20 until you really figure out what your use cases and requirements are going to be. And then you can make a more informed decision about where you're gonna be putting your images. So with all that said, we're going to create this data model in Prisma and we're gonna have an awesome time doing it. So you're gonna make a one-to-one relationship here,

05:39 another one-to-many relationship here, and there will be some duplication between these two tables and that's totally fine. So have a crack at it and we'll see you when you're done.