Library Management System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a library management system. So you are in the process of building your own library management system? Here is my take on a simple version of the system, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

LIBRARY MANAGEMENT SYSTEM

Yep, that is as simple as it gets. Don’t think it needs a lot of explanation.

  • Users – Library staff and members.
  • Media – Not books, but media. Because a modern library also has magazines, newspapers, CDs, and all kinds of “other media”.
  • Loan – To keep track of media loans.

 

NOTES ON THE RELATIONSHIP

Before not-so-intelligent trolls start to scream “the diagram is not accurate” and “this is not a professional system” – This simple system is for reference only.

  • I am assuming that the loan table can be used for reservations as well. That makes it possible for a single piece of media to have multiple “loan” entries, with multiple reservations into the future. If you allow “loan only”, then it’s a (1,1) - (0,1) relationship.
  • If you restrict one book per member, then it’s a (1,1) - (0,1) relationship once again.

So yep, you are building your own system now. You decide the rules and structure.

P.S. “Professionalism” is not equal to “complexity”. True professionals simplify and streamline complex processes/systems.

 

 

TABLE DETAILS

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key, auto-increment.
user_role VARCHAR Up to you to decide. “A”dmin and “M”ember is a good start.
user_name VARCHAR User’s full name.
user_email VARCHAR Unique, user’s email.
user_password VARCHAR Password. Encrypt in your own system.

Some people may prefer to have separate tables for the staff and members. That is not wrong by any means, but the only question is “does it make sense”.

  • If you need more fields, just add them as required – Address, telephone, gender, etc…
  • But if members have a “very different” set of data than the staff, then it makes sense to have a separate table. For example, membership “start date” and “end date” only apply specifically to members.
  • Alternatively, create a “membership” table with only 3 fields – user_id, date_start, and date_end.

You decide which works the best.

 

 

PART 2) MEDIA

COLUMN DATA TYPE NOTES
media_id INTEGER Primary key, auto-increment.
media_code VARCHAR ISBN, ISSN, EAN, UPC, or whatever unique code the media has.
media_name VARCHAR Name of the media.
media_description VARCHAR Short description of the media.
media_status VARCHAR Current status of the media. “A”vailable, “O”n loan, “L”ost is a good start.

As above, this table is called “media” because a library can possibly have “more than just books”.

  • The reason why we are not using media_code as the primary key is simple. There can be multiple copies of the same media.
  • Feel free to add more fields as required – Media type, date acquired, date discarded, notes, etc…
  • Some people may be thinking of creating a view from the loan table to get the latest status of the media instead. It may work, but also be prepared that things can get complicated. How do you cater to media that are “lost, discarded, sold”?
  • Personally, a “semi-manually updated” media_status field makes more sense. When working with the loan table, a server-side script or stored procedure can also update the media_status. At the same time, this also leaves room for library staff to manually update it.

 

 

PART 3) MEDIA LOCATION

You guys who are sharp enough should have noticed that the locations of the media are sorely missing. This is kind of difficult to address for everyone as it is very subjective.

  • A simple library can get past by adding a media_location field to the media table.
  • For some others, it can be branch floor room shelf section.
  • For some others who deal with a mix of physical and digital media, an entirely separate media_location table makes sense.
  • If it is an entirely digital library, location is not even a consideration.

So once again, this is up to you to define.

 

PART 4) LOAN

COLUMN DATA TYPE NOTES
user_id INTEGER Primary and foreign key.
media_id INTEGER Primary and foreign key.
loan_status VARCHAR Up to you to decide. “O”n loan, “R”eturned, and “L”ost, is a good start.
loan_start DATE TIME Start date.
loan_end DATE TIME End date.

Lastly, this table keeps track of the media that are on loan to members.

  • Probably a good idea to add another “loan notes” field, so staff can enter notes like “damaged, lost, late return”.
  • It is also possible to use this table for reservation, just invent your own “reserved status code”.
  • Some people may prefer to create a separate table for reservations, nothing wrong with that too.

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *