title: Database design of a full-featured personal library system, linking titles, authors, serieses, notes, and tags
body: <p>Question: Is there anything in this design that should be changed for correct database practices, or that looks like it might be a problem (E.G. wildplasser's comment on author.*name being a unique requirement).</p>
  
  <p>Question 2:
  Is this the most efficient query for pulling the number of books by each author?</p>
  
  <pre><code>select 
    (select concat(authors.fname,authors.mname,authors.lname) 
     from authors 
     where authors.authorid=bal.authorid
    ) name, 
    count(bal.bookid) as cnt 
  from books_authors_link bal 
  group by bal.authorid order by cnt asc;
  </code></pre>
  
  <p>Background:
  I've played around with this schema for several weeks now, and I want to make sure I'm not digging myself into a pit. I've read tons and tons of posts on StackOverflow, over database design and the like. I've downloaded open-source library management software and checked the database designs.</p>
  
  <p>Looking, I can already see that I can clean up the primary keys of each table...
  I would also like to be able to pull the total number of books by each author,.</p>
  
  <p>I've copied my table notes below, above each table name.</p>
  
  <p>Goal:  Library management system for an avid e-book reader. </p>
  
  <ul>
  <li>System will import books owned by said reader, gathering title, authors, serieses, and tags from filename and contents. System will take hashes of file contents, and import them into the database. System will take contents of files and store them by their hashes on the filesystem.</li>
  <li>Reader can browse by author, title, series, or tags. Reader can add tags to a book.</li>
  <li>Reader can edit a book, add it again, and the system will recognize that a new edition or version of the book has been added (title/author match). This would be good for Gutenberg books, where a user wishes to edit the book, and save both versions. In this way, if a reader, say, wants to take a set of books on a trip, all by James Patterson, or books tagged with "award 2011" or "gutenberg", he/she can search for those requirements, and export a clean list of books (optionally converting the books to an e-reader compatible format as well). </li>
  <li>There is the possibility of users sharing books data in the future, though this would not include the book-files themselves.</li>
  </ul>
  
  <p>I know that Calibre already exists, and is a great system for what it does. However, I am attempting to add features, such as lists for serieses, and a product completely available over the web. Accessibility is a large focus as well. Some of my tables are similar to Calibre's, but I have tried to deviate from Calibre's design.</p>
  
  <p>Any thoughts anyone has would be greatly appreciated.</p>
  
  <p>Tables:</p>
  
  <p>unique bookids, referring to a specific book, e.g. one ISBN, which can have several versions and/or formats</p>
  
  <pre><code>create table books (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    bookid varchar(40) NOT NULL, 
    title TEXT, 
    unique (bookid)
  );
  </code></pre>
  
  <p>holds authors (many to many, authors to books)</p>
  
  <pre><code>create table authors (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    authorid varchar(40) NOT NULL, 
    pfix text (50) binary, 
    fname text (50) binary, 
    mname text (50) binary, 
    lname text (50) binary, 
    sfix text (50) binary, 
    unique (pfix (50), fname (50), mname (50), lname (50), sfix (50))
  ) ENGINE = MyISAM;
  </code></pre>
  
  <p>publishers for books</p>
  
  <pre><code>create table publishers (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    publisherid varchar(40), 
    name TEXT, 
    unique (publisherid)
  );
  </code></pre>
  
  <p>holds name of a series, unique series ID</p>
  
  <pre><code>create table series (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    seriesid varchar(40), 
    seriesname TEXT, 
    unique (seriesid)
  );
  </code></pre>
  
  <p>holds tags for books, authors, serieses, and other tags
  virtual specifies a tag that can be used as a series name when exporting books</p>
  
  <pre><code>create table tags (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    virtual boolean, 
    tagtype varchar(20), 
    tagname TEXT, 
    refid varchar(40), 
    parentid varchar(40), 
    instanceid varchar(40)
  );
  </code></pre>
  
  <p>holds notes for instances of a book, e.g. you edit War Of The Worlds, by H. G. Wells, and add a  note to instanceflags, with the edits, e.g. "chapters 1-3 and pages 50-53", flagname="edit", flagvalue="True"
  also good if you get a book, and then get a book in another series or group, e.g. year's best fiction, so each book's data is stored the same, with a note under this table, linking the book by instanceid</p>
  
  <pre><code>create table instanceflags (
    instanceid varchar(40), 
    flagname varchar(50), 
    flagvalue varchar(50), 
    flagnotes varchar(250)
  );
  </code></pre>
  
  <p>holds all book files
  stores single copies of files on filesystem by hash, to save space
  instanceid refers to a specific edit-version of a book
  bookid is a book (refer to books table for matching bookid value)
  hash is md5 hash of file contents
  compressed books are extracted, and noted as such, with there internal paths saved in original_{path,filename}, and extension denoting compressed types</p>
  
  <pre><code>create table files (
    id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    instanceid varchar(40), 
    bookid varchar(40), 
    hash varchar(64), 
    extension varchar(6), 
    original_filepath varchar(255), 
    original_filename varchar(255)
  );
  </code></pre>
  
  <p>links books and authors
  may need to change name of table so as not to inadvertently duplicate Calibre</p>
  
  <pre><code>create table books_authors_link (
    bookid varchar(40) NOT NULL, 
    authorid varchar(40) NOT NULL, 
    unique (bookid,authorid)
  );
  </code></pre>
  
  <p>links publishers</p>
  
  <pre><code>create table books_publishers_link (
    bookid varchar(40) NOT NULL, 
    publisherid varchar(40) NOT NULL, 
    unique (bookid,publisherid)
  );
  </code></pre>
  
  <p>holds links from series to books
  this way, reports can be made of missing books in a series e.g. Women's Murder Club, have 1 and 10, missing 2-9</p>
  
  <pre><code>create table books_series_link (
    bookid varchar(40) NOT NULL, 
    seriesid varchar(40) NOT NULL, 
    seriesnum float DEFAULT 0
  );
  </code></pre>