A many-to-many relationship

Question

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:

Each user may 'subscribe' to a book.
Each book may have many users 'subscribed' to it.
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.

What problem will the JSON idea solve? I can see only problems in the long run with that. — ypercubeᵀᴹ, Nov 04 '14 at 02:41
The attributes of 'highlights' and 'bookmarks' will be stored as JSON. That means, we can get rid of the two tables. — Khalid Hussain, Nov 04 '14 at 02:43
And why is having 2 tables a problem? Potentially you could have your whole database in one, single, huge, json document. Would that be better? — ypercubeᵀᴹ, Nov 04 '14 at 02:45
You can read this (and your friend): Is using multiple foreign keys separated by commas wrong, and if so, why? — ypercubeᵀᴹ, Nov 04 '14 at 02:53
And this as well: Is storing a delimited list in a database column really that bad? — ypercubeᵀᴹ, Nov 04 '14 at 02:54

vijayp · Accepted Answer · 2014-11-04T04:05:30.393

1

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,

edited Nov 04 '14 at 04:05

answered Nov 04 '14 at 03:59

vijayp

2,766
1
16
20

1

So, something like this? – Khalid Hussain Nov 04 '14 at 04:27
yes go with your design – vijayp Nov 04 '14 at 04:42

A many-to-many relationship

1 Answers1