Many to many relationships in MySQL
Vložit
- čas přidán 27. 11. 2023
- Watch video #1 (Bitwise operations) here: • Store 8 feature flags ...
Watch video #2 (storing as JSON) here: • Storing feature flags ...
To learn more about PlanetScale, head to planetscale.com/youtube!
💬 Follow PlanetScale on social media
• Twitter: / planetscale
• Discord: / discord
• TikTok: / planetscale
• Twitch: / planetscale
• LinkedIn: / planetscale - Věda a technologie
Thanks Aaron!
I don't even use Planetscale or MySQL, but these videos are honestly the best SQL-DB related content on CZcams.
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 ;)
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!
CZcams started recommending me videos with Aaron in the Laravel channel too lol
ive just binged watched almost all your videos , im going to do your course next , thanks for doing this
I really appreciate your videos like this!
Props to the ORMs for abstracting this very important relation.
Your videos are consistently amazing Aaron!
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 😊
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!
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.
Love your videos! Thank you!
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
This is exactly how I would handle this. Great video 👍🏾
Learned like 7 new things in 15 minutes
Could you compare the performances among the three approaches?
Very exhaustive and eloquent.
I love these videos.
Ps. I used to find sql boring.
We call them junction tables. Good video!
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?
Thank you Steve
FINALLY THE LAST EPISODE!
Please, make video where you need to track when user had wich flag. Show current flags, or at particular date.
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 ))
But why to do that when I can just make a Boolean columns for each flag in the Users table?
why not have (user_id, flag_id) as the primary key?
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?
Aaron youre the man
Is there any particular reason not to use a composite primary key on the user_flags table?
could the flags table be an enum instead?
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
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.
The foreign keys on the intermediate table were missing. 😅
Thumbs up!
Why are you not using REPLACE in 6:28 instead of on duplicate key
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
Which editor / SQL App is this?
My question too
This video's title needs to be changed so people know that this is part of the feature flag series.
1:26 what program is that?
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.
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).
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!
“If you aren’t using an ORM, you should be!”
_20 primeagan fanboys are typing_