title: Database schema for a library
body: <p>I'm designing a library management system for a department at my university and I wanted to enlist your eyes on my proposed schema.  This post is primarily concerned with how we store multiple copies of each book; something about what I've designed rubs me the wrong way, and I'm hoping you all can point out better ways to tackle things.</p>
  
  <p>For dealing with users checking out books, I've devised three tables: <strong>book</strong>, <strong>customer</strong>, and <strong>book_copy</strong>.  The relationships between these tables are as follows:</p>
  
  <ul>
  <li>Every <strong>book</strong> has many <strong>book_copies</strong> (to avoid duplicating the book's information while storing the fact that we have multiple copies of that book).</li>
  <li>Every <strong>user</strong> has many <strong>book_copies</strong> (the other end of the relationship)</li>
  </ul>
  
  <p>The tables themselves are designed like this:</p>
  
  <pre><code>------------------------------------------------
  book
  ------------------------------------------------
  + id
  + title
  + author
  + isbn
  + etc.
  ------------------------------------------------
  
  ------------------------------------------------
  customer
  ------------------------------------------------
  + id
  + first_name
  + first_name
  + email
  + address
  + city
  + state
  + zip
  + etc.
  ------------------------------------------------
  
  ------------------------------------------------
  book_copy
  ------------------------------------------------
  + id
  + book_id (FK to book)
  + customer_id (FK to customer)
  + checked_out
  + due_date
  + etc.
  ------------------------------------------------
  </code></pre>
  
  <p>Something about this seems incorrect (or at least inefficient to me) - the perfectionist in me feels like I'm not normalizing this data correctly.  What say ye?  Is there a better, more effective way to design this schema?</p>
  
  <p>Thanks!</p>