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.