Want to fetch a page of posts AND their tags in normalized SQL? Either do a left join and repeat all the post values for every tag or do two round-trip queries and manually join them in code.
If you have the tags in a JSON blob on the post object, you just fetch and decide that.
I’m no expert in JSON, but don’t you lose the ability to filter it before your application receives it all? If you had a reasonable amount of data then in SQL you can add WHERE clause and cut down what you get back so you could end up processing a lot less data than in your JSON example, even with the duplicated top table data. Plus if you’re sensible you can ensure you’re not bringing back more fields than you need.
SQL blows for hierarchical data though.
Want to fetch a page of posts AND their tags in normalized SQL? Either do a left join and repeat all the post values for every tag or do two round-trip queries and manually join them in code.
If you have the tags in a JSON blob on the post object, you just fetch and decide that.
I’m no expert in JSON, but don’t you lose the ability to filter it before your application receives it all? If you had a reasonable amount of data then in SQL you can add WHERE clause and cut down what you get back so you could end up processing a lot less data than in your JSON example, even with the duplicated top table data. Plus if you’re sensible you can ensure you’re not bringing back more fields than you need.
In a traditional SQL database, yeah. In various document-oriented (NoSQL) databases, though, you can do that.
Modern relational databases have support for it too including indexes etc. For example postgres.
If you only join on indexed columns and filter it down to a reasonable number of results it’s easily fast enough.
For true hierarchical structures there’s tricks. Like using an extra Path table, which consists of AncestorId, DescendentId and NumLevel.
If you have this structure:
A -> B -> C
Then you have:
A, A, 0
A, B, 1
A, C, 2
B, B, 0
B, C, 1
C, C, 0
That way you can easily find out all children below a node without any joins in simple queries.
The fact that you’d need to keep this structure in SQL and make sure it’s consistent and updated kinda proves my point.
It’s also not really relevant to my example, which involves a single level parent-child relationship of completely different models (posts and tags).