How to write several authors to a database in a table of simple books?

Question

I am wondering how can I save authors (if there is more than one author in one database) in a simple database.

If there is one author in one book, everything is easy. The problem is that when I want to have a table of Books and I want to be able to make simple selections and joins with a table

Books

| id | Title | ISBN | Authors? | --------------------------------- | | | | | | | | | | 

Authors

 | id | Firs Name | Last Name | Short Name | Pseudonym | -------------------------------------------------------- | | | | | | | | | | | | 

I know that I can use separate lines with the same ISBN and TITLE, however I am wondering if it is a good idea if the header can be very large at one time (UTF line 255 lines long / varchar / char).

I know this is probably a very simple problem, so sorry that I have to ask about this :(

Answer 1

Get rid of the authors column in books .

You have many different relationships between books and authors: some books have several authors, and some authors have written several books. And the books have the first, second and third authors, etc. You cannot display author names for a book in some unpredictable order. The authors and the publisher decide the author’s order, not the dbms programmer.

So you need a books_authors table with the following columns

  book_id author_id author_ordinal (a number like 1,2,3 to denote the order of authors) 

You can get a list of authors for a specific book with this query:

  SELECT isbn, title, author_ordinal, first, last FROM books b LEFT JOIN books_authors ba ON (b.id = ba.book_id) LEFT JOIN authors a ON (ba.author_id = a.id) WHERE isbn = '978whatever' ORDER BY author_ordinal 

It would also be wise for you to place a text box named role in your books_authors table if you want your software to be bibliographic-populated. People have different roles in creating books such as "author", "illustrator", "editor", series editor, "contributor", "author of preface", etc. The role column allows you to commit this information.

By the way, most dbms of reverse engineering tools will be much happier if you name your id columns in sequence. Therefore, you should use books.book_id and authors.author_id, not just books.id and authors.id.

Answer 2

Since a book can have several authors, and an author can write more than one book, I would suggest many, many relationships between tables of books and authors.

Add another table that links the two, for example:

Bookauthors

| BookID | AuthorID |

BookID is the foreign key for Books.id and AuthorID is the foreign key for Authors.id.

If you want to return all the authors for this book, you can do something in accordance with:

 SELECT Authors.id, Authors.FirstName, Authors.LastName FROM Authors INNER JOIN BookAuthors ON Authors.id = BookAuthors.AuthorID WHERE BookAuthors.BookID = '123' 

Answer 3

You have two real possibilities:

• Many authors per book, but each author can have no more than one book (extremely unlikely). Thus, you put the foreign key in the Author table, linking this author to the book. You may have two such authors with the same book identifier, but this means that you do not need to duplicate information about the book or, in fact, nothing more than the book identifier.
• Most likely: many authors per book and many books per author. This is called a many-many relationship and requires the addition of another table.

So, think about it for a second: the book has all its details and an identifier. Each author has many details and an identifier. If you want to associate an author with a book, you add a row to another table, for example, call "Credits".

• Each author may have many loans, but each loan is for one author.
• Similarly, each book may have many credits on the cover, but each credit belongs only to this book.

Thus, you have two “one to many” relationships (from the author to loans and from books to loans) and can represent what you need without any inconvenient ambiguous meanings.

Answer 4

You need another table called BookAuthor , you don’t need the Authors column in the book

 BookAuthor ------------ BookID AuthorID 

Where BookID refers to PK (BookID) to Books and AuthorID PK (AuthorID) to authors

 Select b.Title, CONCAT(a.[First Name], a.[Last Name]) As AuthorName From Books b JOIN BookAuthor ba ON b.ID = ba.BookID JOIN Authors a ON ba.AuthorID = a.ID