As nbk mentions, the middle table is known as a bridge table or linking table. JSON is best for structureless or variable structure situations, but not so good when needed to be used in predicates within the context of a relational system.
By stuffing a bunch of values in a single JSON field and then using the IN
clause, you're telling MySQL to treat the values of that JSON field as a predicate which is very similar to as if you wrote a bunch of OR
clauses with each value in it. Having a large number of predicates in this form is generally sub-performant.
Furthermore, you're still dealing with roughly the same amount of data regardless if you store it in a JSON column or in a bridge table, because your templates
table will still be the same number of rows times the same number of item_ids
in the one-to-many relationship they naturally share. (I know there's some data redundancy in practice, but the optimizations of the database system under the hood with normalized data probably make that a moot point from a performance perspective.)
Joining multiple tables together is not a bad thing for performance, especially only a few tables as you mentioned and especially with small tables of only a few hundred thousand rows at most. It's more so an issue if you over-normalize the data and always need to rejoin all the tables back together in every query. You probably wouldn't see any issues even in that case until your data grows significantly more and/or you're joining around 10+ tables together every query (rough estimates).
So at the end of the day you'd be at best ending up with the same performance on average by leveraging a JSON column, and potentially worse off when querying it as a predicate, by sidestepping one of the key things a relational database is meant to do - join tables. :)
Regarding the purpose of a JSON column, it's better used for data points that are variable in nature and structure, that generally need to be returned as part of the SELECT
list (i.e. are commonly returned as part of your queries' data lookups) as opposed to as being part of the JOIN
, WHERE
, or HAVING
clause predicates.
bridge table
, and there are countless tips and hints about it