A many-to-many relationship

database-designmany-to-manyundefined-relations

I'm designing a simple database and have a question regarding a relationship. So far, I have users, books, highlights and bookmarks. The relationship rules are as follows:

  1. Each user may 'subscribe' to a book.
  2. Each book may have many users 'subscribed' to it.
  3. Each user can have highlights and bookmarks for the book(s) he has 'subscribed' to.

The schema I have so far:

enter image description here

I'm having a problem with the last relation. What I have thought about so far was to use a single primary key instead of 2 keys acting as a composite key for the table book_users and then referencing that as a foreign key in the highlights and bookmarks table.

Another method that my friend recommended was to add another field called additions to the table book_users with a field of TEXT and storing the information in JSON format. The latter seems cleaner and I can't forsee any problems but I just want to confirm if this method is alright to implement.

I was wondering if there is a more efficient way to do this?

Thanks for your time.

Best Answer

I think your first design is better with two additional table highlights and bookmarks.

In long run it will be difficult with design to save data in JSON format, Let say you need to search a bookmark or search a highlights, I am thinking what would be sql query for that. or Let say for page you need to show the highlights, How you will show that.

Even if somehow you solve above problem, performance issue will be a concern. The first design offers indexes and even with long run big tables data can fetched quickly.

Check the datatype of id column in table books_users, it would be INT,