Relational table - is JSON recommended?

Question

Trying to implement a relational table that links a user to it's favorite books.

So I have a table with book_id and user_id

Sample Table:

user 1 favourite 1
user 1 favourite 2
user 1 favourite 3

Can't I have something like a JSON array?

user 1 [favourite 1, favourite 2, favourite 3] ?

Performance-wise is it better to do things like in the first example, or the second?

Answer 1

The first solution is a junction/association table and it is the recommended solution for SQL-based relational databases. Basically, you have two entities, books and users. The junction table is a third table that connects them.

SQL provides the functionality for this purpose. Relational databases provide the mechanisms for optimizing performance -- through indexes, column stores, horizontal partitioning, and fancy algorithms -- that make this work effectively, even for very large databases.

Does this mean that JSON structures are never used? Absolutely not. They have their place -- some databases even provide indexing support for them.

However, from the database perspective, JSON structures add additional overhead for extracting values. They also impede optimization. So, such an array within a row is not the first choice for the data representation.

Answer 2

For straight performance out of a SQL database, the join table is better as per Gordon Linoff's answer.

If you're serialising/deserialising complex objects however it is often more performant to store the object as JSON in a field in a table.

I had a project where I had a fully normalised structure to support an advertising schedule. It worked well until one client created a schedule with 40,000 spots in it. The time to save and load the large advertising schedule versus the small schedules was minutes versus seconds.

I changed the structure to store the object as JSON. The time to serialise then save and deserialise then load the large advertising schedule went from minutes to seconds.