Schema Best Practices

[00:00:00]
>> Speaker 1: I have some best practices here for schemas, basically Always add timestamps for tracking the creation and modification times This is very useful, more useful than you think

[00:00:00]
Always enforce those constraints on the database level, so use unique, not null, foreign keys, things like that As far as indexing goes, that's a whole different game

[00:00:00]
That's literally someone's job Databases have query plans where you can give them your queries, and they can see the queries you've been doing and then provide recommendations on what indexes you need to optimize those queries, and that's just a start

[00:00:00]
It goes deeper from there Good job on indexing Use descriptive names This is very helpful I can't stand looking at a database schema where the field name is like "TG" - what's that

[00:00:00]
What does that even mean I can't even infer it Definitely consider how the schema will evolve Think ahead about potential roadmap features and how that might reflect the schema as you're writing it now and how you might prepare for it because migrations are coming

[00:00:00]
Normalize appropriately What does it mean to really normalize Essentially, it's about choosing where to place foreign keys depending on your query patterns

[00:00:00]
It's about picking a side - where you're always going to query from That's the optimal approach, but don't take it so far that you have to completely refactor your backend logic just to query a table

[00:00:00]
Use enums when you can Enums provide a way to restrict values to a predefined set For example, with frequency, instead of allowing any text, you could use an enum like weekly, daily, monthly, yearly, or biweekly

[00:00:00]
This provides normalization and makes it easier for frontend developers to know exactly what values are expected We can use these database schemas in our controller layer to check user input

[00:00:00]
If all fields that satisfy the schema's constraints will be supplied by the user, then absolutely use the schema for validation The only exception is if some fields are automatically provided by the system, not the user

[00:00:00]
Regarding the hardest types of schema or data migrations: The number one challenge is introducing destructive changes The best approach is to make changes additively

[00:00:00]
When you want to modify something, first add the new field, get it into production, then gradually migrate data and stop using the old field Technologies like Planetscale now offer database branching, which makes migrations easier

[00:00:00]
They can detect potential breaking changes and help you manage schema updates While switching database platforms mid-production might not be feasible for established companies, for new projects, these modern database services can significantly simplify migration processes.