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?
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.
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.
