I am currently learning database for an upcoming school semester, So I’m practicing with the books I currently have. I have books by multiple authors and authors with multiple books. Also books with multiple categories.
So I made three tables. A books table (with all the books information, including the author), an authors table (with the authors name, address, ect) and a categories table (since some books have multiple categories). Since some books have multiple authors and some authors have multiple books, I made a junction table called AuthorsBooks and filled it with information. By best practice, is it ok to NOT put authors with only one book in that (AuthorsBooks) table? Also, I created a BooksCategory junction table and put the books with multiple categories into that table. What would be the best way to link those two tables? By book title? Thanks.
Advertisement
Answer
In general your approach sounds correct with a few exceptions.
So I made three tables
Here’s my count, based on your descriptions:
- Authors
- Books
- Categories
- AuthorsBooks
- BooksCategories
A books table (with all the books information, including the author)
This is incorrect. At the point you realized you need AuthorsBooks, there is no reason to have author as part of the Books table. Any column(s) you used to implement that should be removed from Books.
By best practice, is it ok to NOT put authors with only one book in that (AuthorsBooks) table?
Absolutely not. You created the right structure with AuthorsBooks. It supports any number of authors for a book.
Also, I created a BooksCategory junction table and put the books with multiple categories into that table. What would be the best way to link those two tables?
By Keys. Every table should have a primary key, and in your case the keys should be integers that get incremented with each new row.
Here’s an outline of the table structure and standard naming.
Books ----- id integer auto_increment primary key title varchar(150) Categories ---------- id integer auto_increment primary key name varchar(60) BooksCategories --------------- books_id integer primary key (foreign key for Books.id) categories_id integer primary key (foreign key for Categories.id)