Search code examples
database-designentity-relationship

Library Database Design

Hi i've trying to design a library database. Entities are

  1. Book
  2. Magazine
  3. DVD
  4. Member
  5. Librarian

  6. ...

    • Media types can be borrowed by members.Should i create 3 different
      loan tables for all media types or just one loan table.

    • if we think media as object , (book,magazine and dvd inherits
      media ) how can they expressed in E&R diagram.

    • If all copies of a book are borrowed,the book can be reservable.I can control it programatically(using select count(*) from books)
      but i wonder if there is an efficient way to do this.

Solution
    1. You can use one loan table which includes a ref to other tables.
    2. This link http://www.csc.liv.ac.uk/~valli/Comp507/slides23.pdf will be helpful for you.There is a sample.
    3. You can create book table which has a column named total_quantity , and another table book_copies.If a book is borrowed, you can update book.total_quatity--. When lending you can control whether book.total_quantity is zero or not