Database Schemas

[00:00:00]
>> Speaker 1: 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 We're going to do the database setup and schema So let's talk about database schema design and when it comes to schema design, I can guarantee you

[00:00:00]
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, it's like the Lego of database designs

[00:00:00]
It's like they get one of those Star Wars ships and build it with Legos, but it's like that equivalent but for schemas, and it's like, "OK, I'm never gonna do that 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

[00:00:00]
I'm going to give you some insight into how I think about modeling data in general We're going to explore some relational database concepts We're going to understand RDBMSs and their benefits, and we're going to dive deep enough into migrations and build a complete schema for our habit tracking API using Drizzle ORM

[00:00:00]
So understanding Relational Databases First, we need to understand the relational model A relational database is basically the way that I think about relational data as data that have dependencies on each other, right Like, specifically around ownership

[00:00:00]
I think of ownership models, and the obvious one is that users can own pieces of data, but other data can own other pieces of data as well When I think of relations, I think about what data I need and what data owns other data To get to that conclusion, I always think about the features I'm making

[00:00:00]
Some people call this domain-driven development, where they think about domains, which are basically the different parts of the app The way I like to think about it is critical user journeys - basically, if I were to break apart the app I'm making into different user experiences that have a defined start and end, what data do I need to complete that user journey, and how do those data relate to each other

[00:00:00]
If we think about our app, we've got habit tracking I don't have a GUI, so I have to imagine what the GUI would be One critical user journey is: I'm a user, and I want to log in and see my habits That means an ownership model would be a user owning multiple habits

[00:00:00]
That's a one-to-many relationship Even before 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 put data together without a purpose

[00:00:00]
Relational databases, in my opinion, are databases with fixed columns You can think of a column as a field in a JSON object, and the object as a table The table would be a list of those objects, and a column would be one of the fields on the objects They are fixed-width, so they can't expand beyond the set width you define in most cases

[00:00:00]
Relational databases are very strict, very well-defined, and they use unique keys and indexes to show relationships Typically, you'll have a Primary Key - the unique identifier that identifies a piece of data, like a User ID for the users table A Foreign Key is a key from another table that references a relationship

[00:00:00]
For example, in a one-to-one relationship like a person with one house, depending on where you want to query from, you would put the Foreign Key on either the user's table or the home table Think of tables as collections of objects Objects are called rows, columns are the fields on these objects

[00:00:00]
In your JavaScript brain, think of tables as arrays, rows as objects, and columns as fields Primary Keys are IDs, Foreign Keys are somebody else's ID, and relationships define the type of union: many-to-many, one-to-many, or one-to-one.