I recently graduated from university and want to keep on top of my data-orientated skills. I am doing this by setting up my own personal database up on my local laptop using MySQL Workbench.
The purpose of my database is to record what books I've been reading from late 2018. I started from scratch by using ERD to design my database conceptually and understand how their relationships work out between different entities. I have a few many to many relationships defined which leads to my first main question
Are the many to many relationships I've devised logical and make sense?
(I've included a link to my ERD to help you guys visualise my DB)
The way I see it when it comes to devising the many to many relationships is like this:
Many different books can have many different genre's associated to them. Hence the need to make a many to many relationship between the Book table and the Genre table.
Many different books can be read by many different readers and many readers can read many different books Hence the need to make a many to many relationship between Book and Reader.
I'm pretty sure that these relationships make sense logically but a 2nd opinion is always helpful.
The 2nd main question is this:
(Assuming the many to many relationships are correct) How can I make a JOIN which will allow me to visualise the genre's associated to the books within my database and another JOIN to visualise which readers read which books?
I'm used to utilising JOINS within basic table relationships, but I'm not experienced working with many to many composite key tables when designing joins. How do I ensure I link up all the tables properly and get valid records to show?
Yes, you need many-to-many relationships Book/Genre and Book/Reader.
You probably also need a many-to-many for Book/Author. Some books have multiple authors.
To query the authors for books I've read:
SELECT a.*
FROM Reader AS r
JOIN Book_Reader AS br ON r.Reader_ID = br.Reader_ID
JOIN Book_Author AS ba ON br.Book_ID = ba.Book_ID
JOIN Author AS a ON ba.Author_ID = a.Author_ID
WHERE r.Reader_ID = 'BillKarwin'
I cheated a little, because I know br.Book_ID are comparable to ba.Book_ID, and I didn't need any attributes from the Book table for this query, so I skipped the join to the Book table.
The link between Reader and Genre is unnecessary, and will probably result in a data anomaly. For example, if I read a book that is tagged as a science-fiction novel, but there's no link between my Reader record and that Genre, did I really read it?