Database Design
[publisher]
The `publisher` table stores information about a book's publisher and has a one-to-many relationship with a `book` entry, as (a particular edition of) a book has one publisher, but a publisher is almost always associated with many books.
[book_series]
The `book_series` table simply assigns an ID number to the name of a series for reference by a `book` entry. Since this table is only storing the names of series, I could have also just added a `series_title` field to book, however, having a separate table would help reduce typos, and, more impotantly, would make storing additional series metatata (e.g. total books in series) easier in the future. `book_series` also has a one-to-many relationship with a `book` entry, as a book will only be part of 0 or 1 series, while a series will contian more than one book.
[book]
The `book` table stores most of the bibliographic data about the books in my personal library and makes references to the `publisher` and the `book_series` tables. `book` shares a many-to-many relationship with `person.`
[person]
The `person` table stores the name, dates of birth and death, and role ENUM('author', 'editor', 'translator', 'artist') of a person associated with a `book` entity. `person` shares a many-to-many relationship with `book.`
[bibrecord]
`bibrecord` is the junction table between `book` and `person.` `person` is left-joined on `book` four times to result in separate fields for each of the possible roles of a person associated with a book. Because their can be multiple people performing any of the four defined roles, the data is grouped by the book ID, and the names of `people` performing a role are concatenated respectively into a single field of a single row.