Database Schemas

[00:00:00]
>> Scott Moss: I want to get started with the database, and get some of that set up. Let's do that. But before we get into the code, like always, I'm going to talk, so, let me go here. We're gonna do the DB setup and schema.

[00:00:20]
We're gonna do the DB setup and schema. So, let's talk about database scheme of design and when it comes to scheme and design, I can guarantee you. I have seen most of the common patterns, but I am by no means an expert when it comes to database design. Like I get humbled every time I see there's literally a subreddit of people sharing database designs and every time I look at some of that stuff I'm like.

[00:00:37]
Like I get humbled every time I see there's literally a subreddit of people sharing database designs and every time I look at some of that stuff I'm like. It's like the Lego of database designs. It's like they did like the, you know, they get like one of those Star Wars ships and build it with Legos, but it's like that equivalent but for like schemas and it's like, OK, I'm never gonna do that. That's insane.

[00:00:57]
That's insane. I never even thought of that, so there's definitely levels to it, but I also am in the boat of the simpler the better, so I like to simplify. My database, schemas and, you know, I'm gonna give you some insight into how I think about. Modeling data in general, so we're gonna explore some relational Database concepts.

[00:01:12]
Modeling data in general, so we're gonna explore some relational Database concepts. We're gonna understand ORMs and their benefits, and we're gonna dive deep into, not really deep, but deep enough into migrations and build a complete schema for our habit tracking API using Drizzle ORM. So understanding the Relational Databases. First need to understand like the relational model.

[00:01:28]
First need to understand like the relational model. So relational Database is basically. The way that I think about Relational Databases are, is when, well, let's just think about relational data. When I think about relational data, I think about data that have dependencies on each other, right?

[00:01:49]
When I think about relational data, I think about data that have dependencies on each other, right? Like, for instance, specifically around like. Ownership. I think of ownership models and you know, the obvious one is that like users can own pieces of data but other data can own other pieces of data as well, right?

[00:02:04]
I think of ownership models and you know, the obvious one is that like users can own pieces of data but other data can own other pieces of data as well, right? So when I think of relations I think of like, OK, what is the data that I need and what data owns other data and to get to that conclusion I always think about the features that I'm making, you know, I think some people call this like domain driven development where they think about the domains which are basically like. The different parts of the app, the way that I like to think about is like critical user journeys, which are basically like, if I were to break apart the app that I'm making. Into different user journeys as in like different experiences that have a a defined start and end.

[00:02:24]
Into different user journeys as in like different experiences that have a a defined start and end. What is the data that I need to complete that user journey and how did that how did the those data, whatever plural for data is relate to each other? What the hell is plural for data? Is it just data?

[00:02:40]
Is it just data? OK, all right, all right. Data, OK. I mean, I like, I know that, but like when you think about it like, And it doesn't sound right, but it is, it is right, damn, OK, I confirmed that, so.

[00:03:00]
I mean, I like, I know that, but like when you think about it like, And it doesn't sound right, but it is, it is right, damn, OK, I confirmed that, so. I think, I think of the features so if we think about our app, we got habit tracking. I mean, obviously we don't have a GUI, so I'm just, I have to imagine what the GUI would be. You can imagine what the GUI would be, but I wanna be able to like.

[00:03:17]
You can imagine what the GUI would be, but I wanna be able to like. I'm a user and I wanna see one critical user journey is like, I wanna log in and see my habits. So that means an ownership model would be a user. Owning multiple habits.

[00:03:34]
Owning multiple habits. So that's a one user owning multiple habits That's a one to many relationship, right? That's how I think about data model. It's easier for me to think about it from that perspective when I know that's why like even sometimes before I even do data modeling.

[00:03:51]
It's easier for me to think about it from that perspective when I know that's why like even sometimes before I even do data modeling. I'll sketch out a design first because visually if I can see what the app is trying to do I can model the data around that it's really difficult for me to just be like, I just, I just know how to put this data together, like I can't do that. I really need like a purpose for what I'm doing, so that's relational data I think about is like what data owns what, But as how it pertains to relational database. Relational database, in my opinion, I don't know like the correct way of talking about Relational Databases, but to me it's just like a database that has like fixed columns.

[00:04:03]
Relational database, in my opinion, I don't know like the correct way of talking about Relational Databases, but to me it's just like a database that has like fixed columns. So you think of like a column. As like a field in adjacent object I guess and the object as a table or I guess the table would be a list of those objects in a in a column would be one of the fields on the objects they're like fixed width so like they can't expand beyond the set width in which you define them in most cases, but there's ways to like go beyond it but basically. They are very strict, very well defined, and they use, unique keys and indexes to show relationships amongst each other's so typically you'll have something called like a Primary Key You can think of that as like this is the unique identifier that identifies this piece of data like a User ID that is a user's ID would be their Primary Key for the user's table, right?

[00:04:21]
They are very strict, very well defined, and they use, unique keys and indexes to show relationships amongst each other's so typically you'll have something called like a Primary Key You can think of that as like this is the unique identifier that identifies this piece of data like a User ID that is a user's ID would be their Primary Key for the user's table, right? A Foreign Key would be a key from another table, hence the name Foreign. That exists on some other table. This is a reference.

[00:04:37]
This is a reference. This is how I can say, let's say I had a 1 to 1 relationship. Let's say this was a I don't know, a vet, no, she had many dogs. I let let's say this was like a.

[00:04:52]
I let let's say this was like a. Yeah I don't know, some something that's 1 to 1, I don't know, maybe you have one house, so this was like a neighborhood app and you wanted to match. People to where they live, one person can have one house, so that's a 1 to 1 relationship. Well, that would also mean a house can only have one person, so just go with me there, follow me on that, And in that regard, depending on where you want to query from the home side or the user side, you would put the Foreign Key on that table, right?

[00:05:14]
Well, that would also mean a house can only have one person, so just go with me there, follow me on that, And in that regard, depending on where you want to query from the home side or the user side, you would put the Foreign Key on that table, right? So you're like, oh, based off of my app, I'm always querying from the user's table, so I'm gonna put the Foreign Key of the home on the user's table, or maybe I'm querying from the home table, so I'm gonna put the user's key. User's key is the user's Primary Key is a Foreign Key on the home table, or maybe I'm just going to denormalize it and put it on both, because why not? And that's also fine too, right?

[00:05:30]
And that's also fine too, right? So up to some point. These are all the things that I think about when I'm thinking about data, so just give you some insight into my head. But yeah, tables, you could think of those as like collections of objects.

[00:05:50]
But yeah, tables, you could think of those as like collections of objects. These objects are called rows, columns are the fields on these objects. They're called columns, but really in your JavaScript brain, think about tables as arrays, rows as objects, columns as fields. That's literally what I see.

[00:06:06]
That's literally what I see. I don't know if anybody I've seen that show Mr. Robot, like in the first season of that show, like he sees that company and every time the company name comes up. He blurs it out and he sees it as evil company because they're an evil company.

[00:06:24]
He blurs it out and he sees it as evil company because they're an evil company. Every time I see tables, rows and columns, I just see arrays, objects and fields. That's all I see. I don't, I don't even see tables, rows and columns.

[00:06:42]
I don't, I don't even see tables, rows and columns. They don't even make sense to me. So that's all I see. Primary Keys, that's the ID, Foreign Key, that's somebody else's ID and a relationship is.

[00:07:01]
Primary Keys, that's the ID, Foreign Key, that's somebody else's ID and a relationship is. How you know that it's the type of union, many to many, one to many, or 1 to 1.