5 ms·
You don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; an
by hk__2 9y ago
You don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.
- Avshalom 9y agoNot an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS
- voltagex_ 9y agoYep, I can write an INNER JOIN faster than I can remember jq's arcane syntax. If you needed it all in one flat table for some reason, SQLite supports views.
- wongarsu 9y agoTwo tables: Person: {id, name} and Hobbies: {person_id, name}. Then you can "SELECT person.name, array_agg(hobbies.name) FROM person JOIN hobbies ON (person.id = hobbies.person_id)" to get your json representation back (at least with postgresql, "array_agg" isn't in standard SQL) That's a pretty simple join, any real database layout I've seen goes much more complicated and much deeper than that.
- takeda 9y agoYour version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.
- dotancohen 9y agoThe JSON example that it was compared to was also denormalized.
- mythrwy 9y agoExactly. You could put it in one table if you really wanted to (not that it would be ideal by any means).