Dominika is a Python software developer. She graduated from the Warsaw University of Technology with a degree in computer science. At Vertabelo, she creates content as a technical writer. In her free time, she runs, practices yoga, and learns foreign languages.
Learn how to design a database from scratch for a library system. Try your hand at database design!
Are you interested in a job as a database designer? Imagine that we are planning to develop a software application to manage a local library. You need a library database model! The database designer needs to gather business requirements from the client and decide what data needs to be stored and how.
The next step is data modeling. This is the process of building a diagram that represents the data model to be used in our application.
In this article, we are going to get our hands dirty! Let's do what a database designer does and create a database design for a library system using the Vertabelo modeler.
Let's get started!
The design process always starts with gathering requirements. Our library system will have the following features:
Now, think for a moment. What tables do we need? What fields should they contain? How does one table relate to another?
As books are the core element in the library system, our database needs to contain information about them and their authors. The most intuitive way is to create two tables: book and author .
Note that a single book may have more than one author and a single author may write more than one book. It is a many-to-many relationship, so we need an additional table to connect the two. Let's call it book_author .
Let's think about the columns for a bit. A book has a title . An author has a name . We also need a primary key that allows us to identify each table row uniquely. So, we add an id column to each table.
To summarize, the table book will have the following columns:
The table author will have the following columns:
The table book_author will have the following columns:
Let's see how to model this in Vertabelo! Open the modeler and use the toolbar in the upper left-hand corner:
Use this button to add a new table.
To change table properties, such as the table name and columns, click on the table and use the panel on the right:
You can add new columns using the "Add column" button. To make a column a primary key, check the "PK" checkbox next to that column.
Next, we have to create relationships between our tables. We need to connect both author and book to book_author .
Use this button to create a connection between tables in Vertabelo. To modify reference properties, such as its name and cardinality, click on the connecting line and use the panel on the right:
Note that such connections automatically create foreign keys in the tables (columns marked with "FK").
The columns book_id and author_id together create a unique combination. For this reason, we do not need another artificial id column to act as a primary key. Instead, we use a composite primary key that consists of book_id and author_id .
In our library, each book is assigned to a category. A book may belong to only one category: for example, "Adventure" or "Romance."
We create a new table category . This table needs only two columns:
Next, we need to connect it to the book table. Now, each book has a category_id column (foreign key) to let us identify the category to which it belongs. Note we use a one-to-many relationship.
Our library may have multiple copies of the same book. A book may also be published by multiple publishers. To model this, we create two tables: book_copy and publisher .
The table publisher represents the publisher of the book. It consists of two columns:
The table book_copy has the following columns:
The book part is done. Now, we need people to check out our books! We need to store information about library patrons, such as first name, surname, and email. Each member has his or her own library card and an account which may be active or blocked (e.g., if the book is returned late).
We will use one table to store all this data: patron_account . It consists of the following columns:
Right now, this table is not connected to any other table. We will add relationships in the following steps.
We need a way for the patrons to check out or place a hold on (i.e., reserve) specific books. We will store records of checkouts and holds in two tables: checkout and hold . Let's look closely at each of them.
The table checkout has the following columns:
The table hold has the following columns:
Note the hold table is very similar to checkout . The main difference is that the is_returned flag is not present. Why is that?
With checkouts, we need a way to mark whether or not the book has been returned even past the due date (as the book return may be overdue). It is simpler with holds. When the book is checked out by the patron who has placed a hold on it, a checkout entry is created, and the hold end_time is set to the checkout start_time . If a patron does not check out the book in time, the hold simply expires. Nothing more needs to be done.
At times, all copies of a given book may be checked out or be on hold. The patrons may want to be placed on a waiting list and get notified when it becomes available. Let's see how this may be done.
We are going to create a table to save waiting list entries. Let's call it waitlist . This table will have only two columns:
Note we use book_id and not book_copy_id here. This is because we do not know which copy will be available first. So, it makes sense to check all of them. On the other hand, the checkouts and holds are assigned to specific copies.
That said, you may need to create a system in which a patron can place a hold on a specific book, like a specific edition or translation. In this case, use book_copy_id or add columns such as publisher_id and year_published to the waitlist table. For our case, we will stick to book_id .
Last but not least, we need to send notifications to our patrons: to remind them a book should be returned or to let them know the book they have been waiting for is now available in the library.
We need to create one additional table, notifications . This table will consist of the following columns:
Our database system is ready! See the final diagram below:
You may be surprised by how big the diagram is. We have been designing each table step by step, so it probably looked much less complicated then ??
That's all for today! We have created a database model for a library system.
If you want to learn more about database design, make sure to check out 8 Things to Consider When Creating a Physical Data Model. Don't hesitate to use our Vertabelo Database Modeler to create more database designs!
Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts. Subscribe
