多对多自我参照关系 [英] Many to Many Self referencing relationship

问题描述

我正在尝试使用SQL创建一个图书馆数据库，其中一个book_id建议另一个book_id号.

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

我创建的表如下:

   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?

推荐答案

您可以创建一个相关的多对多"表，如下所示:

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)

然后，您可以根据需要添加任意多个条目.在Books表中，假设id 1 ="Huckleberry Finn"，则可以将Book_id1的值添加为1，将Book_id2的值添加为2，其中第二本书为"Tom Sawyer"；然后将"Jaimie McPheeters的游记"添加为另一个连接器，依此类推.

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.

例如，如果它们在BOOK表中:

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)

是否还要插入"2,1"和"3,1"记录取决于您是否要说喜欢Huck Finn的人推荐汤姆·索耶"，以及是否说喜欢汤姆·索耶的人".推荐赫克·芬(Huck Finn)"(或者是否将单向推荐视为双向推荐而未明确记录.)

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).

此后，即使没有"2,3"记录，也应该可以确定book2("Tom Sawyer")和book3("Jaimie McPheeters的游记")之间存在联系.这有点像谚语中的凯文·培根的七个学位"(Seven Degrees of Kevin Bacon)-所有互相推荐的书本身都是互相推荐的.或者，OTOH，您可以仅将连接限制为直接连接("Huck Finn"连接到"Tom Sawyer"，但是"Tom Sawyer"不连接到"Jamie McPheeters"，除非您插入2,3记录.

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.

这篇关于多对多自我参照关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！