sql - Many to Many Self referencing relationship

Question

I'm attempting to create a library database using SQL in which one book_id recommends another book_id number.

The table I have created is as follows:

   CREATE TABLE BOOK (
   Book_id INT NOT NULL,
   Book_name VARCHAR(40) NOT NULL,
   Description VARCHAR (100) NOT NULL,
   Image BLOB NOT NULL,
   Category_id INT NOT NULL,
   PRIMARY KEY (Book_id),
   FOREIGN KEY (Category_id) REFERENCES Category (Category_id)

Do I need to create a separate table in order to enforce this many to many self relationship?

Answer 1

You could create a related Many-to-Many table like this:

CREATE TABLE RECOMMENDED_BOOKS (
   Book_id1 INT NOT NULL, // FK related to BOOK.Book_id
   Book_id2 INT NOT NULL), // FK related to BOOK.Book_id
   CONSTRAINT pk_RecommendedBooks PRIMARY KEY (Book_id1, Book_id2)

You could then add as many entries as you want. Say id 1 = "Huckleberry Finn" in the Books table, you could add 1 as the value of Book_id1 and 2 as the value of Book_id2, where book 2 is "Tom Sawyer"; then add "The Travels of Jaimie McPheeters" as another connector, etc.

e.g, if these are in the BOOK table:

Book_id 1, Book_name "The Adventures of Huckleberry Finn"
Book_id 2, Book_name "The Adventures of Tom Sawyer"
Book_id 3, Book_name "The Travels of Jaimie McPheeters"
Book_id 4, Book_name "Westward Ho"
Book_id 5, Book_name "Main Traveled Roads"

...you could add these records:

INSERT INTO RECOMMENDED_BOOKS (Book_id1, Book_id2) VALUES (1, 2)
INSERT INTO RECOMMENDED_BOOKS (Book_id1, Book_id2) VALUES (1, 3)

Whether you also inserted "2,1" and "3,1" records would depend on whether you want to be able to say "people who liked Huck Finn recommend Tom Sawyer" and also say, "people who liked Tom Sawyer recommend Huck Finn" (or whether a one-way recommendation is to be considered two-way without explicitly recording that).

It should be thereafter possible to determine that there is a connection between book2 ("Tom Sawyer") and book3 ("The Travels of Jaimie McPheeters"), even though there is no "2,3" record. It's kind of like the proverbial "Seven Degrees of Kevin Bacon" - all books with a mutual recommendation are themselves mutual recommendations. OR, OTOH, you could just keep your connections restricted to direct ones ("Huck Finn" connects to "Tom Sawyer" but "Tom Sawyer" does not connect to "Jamie McPheeters" unless you insert a 2,3 record.

etc. - you get the point, I'm sure.