CODEWITHSUNDEEP
javamysqlspringhibernatejpa
Vinci
Vinci

Reputation: 341

MySQL Database schema design and relationships

I'm designing a project, where logged users can add rating to any of the books they read, and also add status to any book they want to read/read/will read. So i created tables: Book, User, Rating, Status and I'm not sure how to connect all of these informations in one table (and if it's possible).

Right now, I have user_books table where I store foreign keys to status_id, rating_id, book_id and user_id, but I'm not sure if it is a right way to do this.

Relationships that I want to create in my SpringBoot application:

enter image description here

Upvotes: 0

Views: 457

Answers (1)

user3470294
user3470294

Reputation: 190

looks you want to create a many-to-many relationship. Logically many-to-many relationships do exists, but when it comes to Physical model, you create a mapping table, which you already did it -> user_books table. Based on the id from the mapping table, you can get these 1) Number of users read "that" book. 2) Highest ratings on a book. 3) Ratings based on a book on a user age group (if the user table has age)

Upvotes: 0

Related Questions