sql - How do you deal with m..n relationships in a relational database?

Question

Let's look at an example - books. A book can have 1..n authors. An author can have 1..m books. What is a good way to represent all of the authors of a book?

I came up with an idea to create a Books table and an Authors table. The Authors table has a primary AuthorID key the author's name. The Books table has a primary Book ID and metadata about the book (title, publication date, so on). However, there needs to be a way to link the books to authors and authors to books. And this is where the problem is.

Let's say we have three books by Bob. However, on one book, he wrote it as Bob, PhD. Another he wrote as Dr. Bob, and a third he wrote as Dr. Robert. I want to be able to identify the fact that these authors are, in reality, the same person but credited under different names. I also want to distinguish Bob from another Bob who wrote different books.

Now let's also add in another part to an application, a Person table that keeps track of interesting people. And let's say that Bob is an interesting person. I want to not only say that the author of all three books is Bob, but that this interesting Bob is the same Bob as the author Bob.

So what strategies exist for such potentially complicated mapping, while ensuring that the book authors are identified by the name on the cover?

Answer 1

Add another table called BookAuthors with columns for BookID, AuthorID, and NameUsed. A NULL value for NameUsed would mean to pull it from the Author's table instead. This is called an Intersection table.