Thanks Aaron! I don't even use Planetscale or MySQL, but these videos are honestly the best SQL-DB related content on UA-cam. It really takes a lot of presentation skills to make these videos that informative and entertaining. Keep it up!
There's even more benefits to this that, IMO, make it simply the best option. One is the lack of data duplication in the DB. Since we're doing this in a very normalized and RDBMS-friendly way we're really storing the minimum amount of data to make it work. The JSON method has a ton of repeated data, stored as strings. You might see "dark mode": true for thousands of rows. Another benefit is data integrity. Leveraging the DB to enforce a schema is usually the correct choice. We can't insert a flag that doesn't exist, we can't insert for a user that doesn't exist. With JSON there's nothing stopping garbage data from being inserted, leading to silent bugs. The application then has to enforce the schema... programmatically. "dar mode": true is just a bit too easy to slip by.
Not as long as there’s a combo unique constraint that limits to 1 of each pair. Otherwise, if you could apply the relation multiple times, then it could be helpful to have a separate id on the join table so you can tell them apart.
Brilliant as always. I'm a big fan of many to many like this, so flexible. But I do also like the json method for some stuff too. Bitwise is just too mathy lol.
Linking tables are very useful for course enrolment system (student-course, teacher-course, room-course, .certificate_template-course ..) just to name a few example of many to many relationships. I was always call it a Linking table until I read somewhere that it's called a Bridge table. the same meaning though. Thanks Aaron!
One interesting approach to getting the data in an array is using json functionality, ive only ever seen it in drizzle and using postgres, i went through the generated query and its pretty performant plus the database is probably faster at json stuff than my js application
Why did you use unique constraints? Why not composed primary key? When would you use a surrogate 'id' column vs composed primary key? Would like your opinion on this ))
One again great video! My boss says you should never ever create queries from the application. You should always use stored procedures for performance and security reasons. Is there any weight to this argument?
I don't understand why you have a separate primary key in `users_flags` - shouldn't that just be the unique combination of user and flag? Also, the flag ID index is also required for a foreign key constraint on flags, not just for indexing flag ID alone.
This was one of my questions as well, the primary key one. Also I think that the foreign key is optional, depending on if you need to use the constraint for cascading operations, referential integrity itself can be handled in the application layer.
Having a seperate primary key helps when using a db management app for update delete tasks etc.. without primary key That wont work. There mey b other uses also.
@@sudeshryan8707 Yes, you can use a separated id primary key or use user_id + flag_id as compound primary key directly (instead of adding a UNIQUE index).
Doing referential integrity in the application layer is a big no-no. Foreign key constraints are there exactly to avoid that. In this case, there should be a foreign key between both user_flags => user and user_flags => flag@@mme725
In your JOINs video, you didn't show your EXPLAINS or schema & you got some details wrong. The IN and EXISTS queries had the same exact execution plan. The JOIN created a temporary table of posts (presumably with > 90,000 views). You described the join as duplicating results bc of the DISTINCT filtering, but I'm just not sure that's true from my tests.
So the options .... 1. Pretty savi , not gonna use it or next year I'll put a cross over my head 2. interesting, useful when you don't know the actual structure or maybe you want json back into your app 3. ORM's way love it, currently what I use, not by choice but that I find it more logical. to have everything separate. Thank you for the serie, I did learn some new stuff in the first 2 videos!
I was always told that when you do an EXIST (or NOT EXIST) sub-query, you have to use SELECT 1 instead of SELECT * (for performance reasons). Is that true? or it does not matter at all?
What about using an enum in the "relation table" instead of using a full table for the flags ? For me it has the benefice of searchign them by name instead of using the id, and you can have autocomplete, I lose on having a description though.
From what I understand, you have to do an ALTER TABLE command to add or remove enum values, which rewrites the whole table. Not only that, it'd be tied to one table. Using a separate means you can add and remove without messing with any tables using the flags, plus you can use the flags on multiple tables
My insert statement went from 20 seconds to 2 seconda when writing my own SQL instead of dumb ORM doing the same select 30 times instead of 0 times. A lot of similar problems in other parts of the app. It's really wierd that you suggest using ORMs, given that the rest of stuff you make is excelent quality, ORMs are just not it. DX is awesome, but the performance is atrocious in my case (and many other devs agree).
Thanks Aaron!
I don't even use Planetscale or MySQL, but these videos are honestly the best SQL-DB related content on UA-cam.
It really takes a lot of presentation skills to make these videos that informative and entertaining.
Keep it up!
+1 to everything you just said. I primarily use mongodb and I’m still enthralled by this content 😂
100 percent
For real. Insta subscribed even if I don't use planetscale. Great stuff!
Same. I use sqlite and postgres and I still find these usefule
Great video! Please, because these videos are related put a part 1, 2, and 3 on the title for future users to find them easily.
algorithm doesn’t like part videos
@@riccardopalleschi Hehe, ok let's put them in an array ;)
UA-cam started recommending me videos with Aaron in the Laravel channel too lol
There's even more benefits to this that, IMO, make it simply the best option. One is the lack of data duplication in the DB. Since we're doing this in a very normalized and RDBMS-friendly way we're really storing the minimum amount of data to make it work. The JSON method has a ton of repeated data, stored as strings. You might see "dark mode": true for thousands of rows. Another benefit is data integrity. Leveraging the DB to enforce a schema is usually the correct choice. We can't insert a flag that doesn't exist, we can't insert for a user that doesn't exist. With JSON there's nothing stopping garbage data from being inserted, leading to silent bugs. The application then has to enforce the schema... programmatically. "dar mode": true is just a bit too easy to slip by.
PlanetScale is becoming the gold standard of SQL tutorials. Did not see that coming!
ive just binged watched almost all your videos , im going to do your course next , thanks for doing this
Is there any downside of not having the `id` column on the lookup table and instead having [`user_id` and `flag_id`] being a primary key?
Not as long as there’s a combo unique constraint that limits to 1 of each pair. Otherwise, if you could apply the relation multiple times, then it could be helpful to have a separate id on the join table so you can tell them apart.
I really appreciate your videos like this!
Your only video that gives me an in-depth understanding of SQL and stuffs. I just wish you can do a full tutorial on MySQL 😊
Props to the ORMs for abstracting this very important relation.
Brilliant as always. I'm a big fan of many to many like this, so flexible. But I do also like the json method for some stuff too. Bitwise is just too mathy lol.
We call them junction tables. Good video!
This is exactly how I would handle this. Great video 👍🏾
Very exhaustive and eloquent.
I love these videos.
Ps. I used to find sql boring.
Could you compare the performances among the three approaches?
Linking tables are very useful for course enrolment system (student-course, teacher-course, room-course, .certificate_template-course ..) just to name a few example of many to many relationships.
I was always call it a Linking table until I read somewhere that it's called a Bridge table. the same meaning though.
Thanks Aaron!
ORMs get a lot of hate in some circles, but you seem to be a strong advocate for it. Could you make a video about why you should use an ORM?
Your videos are consistently amazing Aaron!
One interesting approach to getting the data in an array is using json functionality, ive only ever seen it in drizzle and using postgres, i went through the generated query and its pretty performant plus the database is probably faster at json stuff than my js application
Love your videos! Thank you!
Learned like 7 new things in 15 minutes
Why did you use unique constraints? Why not composed primary key? When would you use a surrogate 'id' column vs composed primary key? Would like your opinion on this ))
One again great video! My boss says you should never ever create queries from the application. You should always use stored procedures for performance and security reasons. Is there any weight to this argument?
FINALLY THE LAST EPISODE!
I don't understand why you have a separate primary key in `users_flags` - shouldn't that just be the unique combination of user and flag? Also, the flag ID index is also required for a foreign key constraint on flags, not just for indexing flag ID alone.
This was one of my questions as well, the primary key one.
Also I think that the foreign key is optional, depending on if you need to use the constraint for cascading operations, referential integrity itself can be handled in the application layer.
Having a seperate primary key helps when using a db management app for update delete tasks etc.. without primary key That wont work.
There mey b other uses also.
@@sudeshryan8707 Yes, you can use a separated id primary key or use user_id + flag_id as compound primary key directly (instead of adding a UNIQUE index).
You define it is a primary key, you just do PRIMARY(user_id,flag_id), so that would still work.@@sudeshryan8707
Doing referential integrity in the application layer is a big no-no. Foreign key constraints are there exactly to avoid that. In this case, there should be a foreign key between both user_flags => user and user_flags => flag@@mme725
why not have (user_id, flag_id) as the primary key?
Why are you not using REPLACE in 6:28 instead of on duplicate key
Which editor / SQL App is this?
My question too
But why to do that when I can just make a Boolean columns for each flag in the Users table?
Please, make video where you need to track when user had wich flag. Show current flags, or at particular date.
Is there any particular reason not to use a composite primary key on the user_flags table?
Thank you Steve
In your JOINs video, you didn't show your EXPLAINS or schema & you got some details wrong.
The IN and EXISTS queries had the same exact execution plan. The JOIN created a temporary table of posts (presumably with > 90,000 views).
You described the join as duplicating results bc of the DISTINCT filtering, but I'm just not sure that's true from my tests.
So the options ....
1. Pretty savi , not gonna use it or next year I'll put a cross over my head
2. interesting, useful when you don't know the actual structure or maybe you want json back into your app
3. ORM's way love it, currently what I use, not by choice but that I find it more logical. to have everything separate.
Thank you for the serie, I did learn some new stuff in the first 2 videos!
The foreign keys on the intermediate table were missing. 😅
I was always told that when you do an EXIST (or NOT EXIST) sub-query, you have to use SELECT 1 instead of SELECT * (for performance reasons). Is that true? or it does not matter at all?
MariaDB (MySQL fork) completely ignores 1 or any select columns. * Is correct.
Aaron youre the man
1:26 what program is that?
could the flags table be an enum instead?
What is the name of the software that he used to execute command ?
TablePlus!
What about using an enum in the "relation table" instead of using a full table for the flags ?
For me it has the benefice of searchign them by name instead of using the id, and you can have autocomplete, I lose on having a description though.
From what I understand, you have to do an ALTER TABLE command to add or remove enum values, which rewrites the whole table. Not only that, it'd be tied to one table. Using a separate means you can add and remove without messing with any tables using the flags, plus you can use the flags on multiple tables
@@FunkatronicGeek I trust you on there, I am helped by prisma a lot on this point, it is a lot easdier for me to use enums
My insert statement went from 20 seconds to 2 seconda when writing my own SQL instead of dumb ORM doing the same select 30 times instead of 0 times. A lot of similar problems in other parts of the app. It's really wierd that you suggest using ORMs, given that the rest of stuff you make is excelent quality, ORMs are just not it. DX is awesome, but the performance is atrocious in my case (and many other devs agree).
“If you aren’t using an ORM, you should be!”
_20 primeagan fanboys are typing_
This video's title needs to be changed so people know that this is part of the feature flag series.
Thumbs up!