In this video, we'll design a database for a library management system.
You can follow along to see how a database design can be created based on a set of requirements.
We'll finish with an Entity Relationship Diagram which is a diagram that shows the tables,
columns, and how they are related.
I'll have a link to the final ERD in the description below, along with timestamps, if you want
to skip back and forth.
For this example, we need to design a database for a library management system.
This system will be responsible for managing books that each library member has borrowed
and returned, as well as the list of books the library has in stock.
We'll start off with a set of requirements.
In a real software development team, you'll gather these early in the process, and they
will get clarified and enhanced as you design the system.
For our example, we have a list of requirements that we will need to meet.
Here are the requirements our database needs to meet.
So how do we get started?
I like to start by identifying the nouns in the requirements, which are things.
We can look at the requirements and see a few: book, member, author, fine.
Each of these would likely be a table in our database.
We can start our diagram by placing these items on the diagram.
We only have the names for now: book, member, author, fine.
It's up to you whether you name them singular, such as book or member, or plural, such as
books or members.
There are pros and cons either way, and it comes down to personal preference.
I prefer the singular name where possible, but if you want to use plural then go for
it.
The process I'm following is one I've done many times, and I go into more depth inside
my Relational Database Design course inside my Database Star Academy.
The Database Star Academy is a membership that allows you to improve your database and
SQL skills.
There are a whole range of courses, practice exercises, and guides in there.
You can find out more with the link in the description, or visit www.databasetar.com/dsa.
I just wanted to mention that in case you found this process a bit rushed.
Let's improve our database by adding some attributes.
We'll start with the book table.
We can see in the requirements that a book has a unique ID.
We can also see that books can be searched by a few fields: title, author, category,
and publication year.
So let's add them to the book table.
Here's our updated ERD.
Our book table has been enhanced to have a few more attributes.
We've added a primary key indicator to the unique ID, which will help when relating books
to other tables.
Let's check our requirements again.
We've met requirement 1 and 2 here with the book attributes.
What about requirement 3?
A book can have multiple authors.
Let's see our ERD.
Our ERD has the author as an attribute of a book.
In this design, a book can only have one author.
How can we add multiple authors?
We could add the second author into the same field, but that is not ideal.
It will be hard to process two values in a single field, and there would be some duplication
across rows.
Ideally, we would have a table for authors, and we can relate authors to the books they
have written.
Here's our ERD with a separate author table.
I've added a primary key called id, because any time we need to create a new table and
join it to others, it's a good idea to have a primary key to identify the rows.
What else do we need for an author?
I've added a first name and last name.
These were not mentioned in the requirements, but they are an assumption that an author
has a name.
If this was a real project we can clarify the fields to store.
So how do we relate this author table to the book table?
We know that a book can have many authors.
An author can also write many books.
This is called a many-to-many relationship.
I've created a video on this concept in more detail which you can check out in the description,
but for our design it means we need a joining table in the middle that stores the combinations
of books and authors.
Here's our design with the joining table.
We have a table called book_author, with the primary key of the book and the author table
in this table as foreign keys.
Let's check out our requirements again.
We've achieved the first 3 requirements.
Requirement number 4 says there may be more than one copy of the book in the library.
So each book would need a number of copies stored for it, which would represent the number
that the library owns.
We won't need to store the number they have in stock, because this can be calculated based
on the number they own and the books that have been borrowed.
Here's our updated diagram.
We have a new field called copies_owned in the book table, which stores the number of
copies of that book the library owns.
What's the next requirement?
Requirement 4 is met.
Requirement 5 is about borrowing books.
A member can borrow a book and the date of the borrowing is stored.
We have two concepts here: a member, and a record of the books they have borrowed.
Let's add them to the database.
We create a new table called member to store the members of the library.
We'll give it a primary key of id, and a first name and last name.
Once again, this is an assumption, but a fairly safe one.
We'll also add in a new table to store books that have been borrowed.
Rather than borrow, we'll call it loan.
Borrow is the verb, the action that is taken.
Loan can be a verb as well, but I see it as a noun.
A member takes out a loan on a book.
The table names here can obviously change if you don't like them, so if you're doing
this yourself, feel free to use a different name.
For a loan, we need to store a few things.
We'll have a primary key.
We need to store the book that was borrowed, the member that borrowed it, and the date
it was loaned.
Now let's see the next requirement.
This one, number 6, is about what the librarians can see.
They should be able to see who has borrowed a book, which they can already do with our
design.
They can see who has checked out a book in the past, which they can see by looking at
older records in the loan table.
They can't see when members have joined or the status of a member.
Let's add that to our design now.
We can add a field called joined_date to the member table to store the date a member joined.
This would satisfy that requirement we just saw.
We can also add a status field.
This can be used to store whatever statuses are needed, such as active, cancelled, or
suspended.
Now I think we've achieved that requirement.
What else do we need to do?
There are two more requirements to go.
Requirement 7 here says that members can reserve copies of books.
What would we need to store for a reservation?
I think we would need the book to reserve, the member reserving it, the date they placed
the reservation, and whether the reservation was cancelled or fulfilled, so some kind of
status.
And, of course, a primary key.
Here's our updated ERD.
We've added in a reservation table which is related to the book table and the member table.
Reservations would be added to this table to keep track of them.
The final requirement here is about fines.
Fines can be imposed on members if they don't return a book within 7 days, and the fines
are added to their account for them to pay.
How can we capture this?
We could have a new table for the fines that a member has received, and another table for
payments.
These two tables can be used to calculate any outstanding fine balance for a member.
Let's add this to our database.
We've added a fine table to our database here.
Along with the primary key, we've stored the member who got the fine, the loan that the
fine is for, the date of the fine, and the amount.
Using these relationships we can work out and display a whole range of information about
the fine.
What about payments?
We've got a new fine_payment table here, which captures payments that a member has made.
We store a primary key, the member who made the payment, the payment date, and the amount
of the payment.
There's no need, I think, to relate it to a specific fine or loan.
Payments can be made independently of fines.
We also need a way to record when a loaned book was return.
One way to do it is to have a returns table that stores returns of items.
Another way would be to update the original loan when an item is returned.
I'll go for the second option, as it seems simpler, and we can use the existing table.
We could add a returned_date field to the loan table.
So we've got a pretty good database design here.
It should meet all of our requirements.
But there's one more thing we can do to make it better.
There are several columns in our tables that can be set to one of a range of values.
A book category, for example.
The category is limited to a range of values, such as Business or Travel or Fiction.
If we leave it as a simple text field, then there's a chance that there would be some
inconsistencies in the values.
We would see the same thing for the reservation status and the member status.
How can we improve this?
We can create a separate table that contains a list of the valid values, and relate it
to the main table.
For example, a new book category table, which contains an ID and a category.
The book table would then link to the ID in this book category table.
This way, the list of values is enforced and is only stored in one place.
Let's make that change now.
We've added a book category table here with an id and a category.
We've also linked it to the book table using the ID.
This would improve our data quality and also allow us to meet our requirements.
We can do the same thing with the reservation status and the member status, where we create
a separate table for the status values and link them using the ID.
So this is our final ERD for the library management system.
It meets the requirements we have, and we've used a couple of techniques to improve the
quality of the data.
If you have any questions on this design, or any issues with it, or notice something
I've done wrong, let me know in the comments below.
There's a link to the image of this ERD in the description as well, if you want to save
it to your computer.
That brings us to the end of this tutorial on creating a database design for a library
management system.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit [databasestar.com](http://databasestar.com/).
That’s where I share my best database-related content.
Which part of this walkthrough was the most helpful for you?
Was it identifying the nouns to begin the design?
Or the way that we moved the status and category values to a separate table?
Or something else?
Thanks for watching.