When talking about n to m relationships in a database, an intersection table is often mentioned. E.g. there are authours and books, an author can have a relationship to many books and vice versa.
But: How can I model the relationship, that the authors can also have relationships among them and books, too?
E.g. - Author A has written Book 1 and Book 2, and Author A is a friend of Author B. - Book 1 is related to the same genre as Book 2.
Do I have multiple Intersection tables then? E.g. An Author - Book Relationship table, an Author -Author "Friend"-Relationship table and a Book-Book "Genre" Relationship?
I am a beginner concerning databases and SQL... Thank you for your help, Anne
To create a simple 1:n relation-ship, it's enough to have one FOREIGN KEY-column to specify the related record.
But whenever you want to model a m:n relationship, or you might need to add additional information to a 1:n relationship, you'll need a mapping table.
1:n relations: Imagine the case, that a book can have exactly one current reader. If you'd use a FK CurrentReaderID in the Book table, you would not be able to find previous readers once you've changed the FK-value.m:n relations: Think of the relation as an object on its own with its own specific detail data (when, validTo, createdBy...)And yes: In a well designed database you'll find many tables with a wide range of different relations / mappings among them.