Database Design for a Library Management System

[@DatabaseStar]

If you want the SQL to create the tables in this design, you can refer to the link in the description. A few comments below had asked for this.

[@IAmPhysics1]

For the fine system, one could apply a bit of accounting knowledge and keep it all in one table. To do this, I would structure the fines table like this: * (PK) id * (FK) Member_id * Loan_id //Remove FK constraint. Payments can be independent of a specific loan ID. * Fine_date * Fine_type //This is the first big change. Define whether the fine is the initial fine, or compounding interest. Some libraries do that. * Fine_debit //For increases to the fines owed, both initial and interest. * Fine_credit //For decreases to the fines owed. This would essentially structure the fines table to be like an accounting ledger, which is a tried and true layout for auditing any sort of money movement.

[@DatabaseStar]

Great idea! This does have the benefits you mentioned, such as increasing and decreasing the fine amount.

[@houstonfirefox]

For those wondering, the primary key 'ID' on book table is used so that duplicate book names can be represented, with other fields to help the user differentiate which they are looking at.

[@DatabaseStar]

Good point! And also if the book name or anything else changes it can be updated without impacting any links to other tables.

[@houstonfirefox]

@@DatabaseStar Also a good point! Primary key changes are to be avoided at all costs. Using a surrogate key as is done here is certainly preferable to using possibly-changing data in the key. Very nice work sir!

[@vinny142]

@@houstonfirefox " Primary key changes are to be avoided at all costs." That's a bit strong because you can define ON UPDATE CASCADE on the relations and any change in the PK value will be automatically passed to the related tables. This may result in an unexpected or undesirable amount of tuple-writes, but that is a different matter. You cannot break your data by changing a PK value, the "MS" bit in the RDBMS will not let you.

[@houstonfirefox]

@@vinny142 True and for the most part, I agree. However, many of the so-called 'professional' pieces of software I deal with perform integrity-checking on the front end (application) and inconsistent/little-to-any on the back end (database side). As such, there are many times where there is no protection against a PK change in the primary table resulting in a lot of 'orphaned' data in the related tables. At our company we recently saw this happen to our $250,000+/year CRM system which created quite a nightmare to sort out (and retroactively update). Developers are a whole other story as you know hahaha. Of course, one of the downsides of a surrogate key is when transporting the data between databases using a script. If the 'auto-id' or 'identity' is not disabled prior to the script running then it may result in a renumbering of the surrogate key and all of the fun that ensues after that. Thanks for the reply! I enjoy the discussion!

[@alejandrocarmena8767]

Hi, very insightful video :) just one question. Since "member_id" is already found in "loan", why is it duplicated in table "fine"?

[@DatabaseStar]

Thanks! Good question. I think you're right, you don't actually need the member_id in the fine table. You can find the member that it is for by looking at the related loan record. And having it in the fine table, in my design, leaves it open for the member ID to be different which could be different.

[@karanpaul5525]

How can we use this final ERD design to create the tables in SSMS? I mean how to code just by observing the final ERD?

[@rajatssphere8781]

Yes! Exactly I am still figuring out how to write the create and alter table queries just by looking at the final design. It's so confusing

[@DatabaseStar]

Great question! I'll add the code for this to this video (perhaps as a comment with a link to it). In short, you write a range of Create Table statements, but I can see how it can be confusing.

[@karanpaul5525]

@@DatabaseStar thanks a lot 🙏

[@DatabaseStar]

@Karan Paul I've added a link to the description with the SQL code to create the tables. Hope that helps!

[@dishasolanki56]

Why fine_payment is not associated with loan ? If there is something like "Find top10 books for which max fine has been paid", with the current design that is not possible.

[@DatabaseStar]

I chose not to relate fine_payment to loan because the fine table is already related to loan, and the fine payments could be recorded independently and be for one or many loans. But it would be just as valid to relate fine_payment to fine, which would be done by putting the fine.id into the fine_payment table. You could "find the top 10 books for which max fine has been paid" by joining through the member table to the fine_payment table to see the payments, but yes it would be a bit trickier.

[@syedburhanalishah1631]

Very well done Sir. It would be beneficial if you could show Identifying and Non-Identifying relations in the ERD too. I still find it difficult to grasp. Or if you could please make a video about Strong & Weak Entities and Identifying & Non-Identifying relations.

[@DatabaseStar]

Thanks! I don't often come across those concepts when creating ERDs.

[@syedburhanalishah1631]

@@DatabaseStar Is that because we are now using surrogate keys in every table ?

[@m_forever7]

Is there any coding available for this database ? I need it for a project.

[@DatabaseStar]

Hi, you can use the final design at the end of the video to create the tables. You can write a range of Create Table statements to create the tables in your database.

[@DatabaseStar]

@Meenakshi - I've added a link to the description with the SQL code to create the tables. Hope that helps!

[@mehmetyasirsaher]

Hi sir! Thanks for the video it really helped me and I learn a lot of basics about SQL from this project. However, I failed to create queries. Could you help me? I need some examples. Thank you for everything.

[@DatabaseStar]

Good to hear! Sure, you can check out the SQL Roadmap mentioned in the description to see how to start creating SQL queries.

[@Modi_In_Canada]

Let's say I have 10 copy of a book.. How can I get status of each book?

[@DatabaseStar]

You could use a combination of the loan table and the book table to determine how many books are currently loaned out. This design doesn't track each individual copy of the book, but you could tell from this design how many of the 10 books are currently loaned out.

[@kumarraot]

Input: You can have check constraint on the fields like status, category etc. NO need to maintain another table for same. ALTER TABLE [TableName] ADD CONSTRAINT my_constraint CHECK (Status = 'in stock' OR status = 'borrowed')

[@DatabaseStar]

Yeah that’s true, you can use a check constraint. They are harder to edit or add new values to as you need to run an Alter statement instead of just editing table data. But if the possible values rarely change then it’s not that much of an issue and a check constraint would be better.

[@trupti6251]

Is it correct if we have a status in fine_payment which will contain whether the payment is due or paid ?

[@DatabaseStar]

Yes that can work

[@MuhammadHuzaifaKhan-o3s]

How do I know that which fines are pending for the specific member?

[@DatabaseStar]

You can select from the fine table and filter on a specific member ID. There isn't a way to link the specific payment to the specific fine, but you can calculate a total to see the outstanding balance.

[@puneetyadav492]

Thank you

[@DatabaseStar]

You're welcome

[@Photonsr]

Is this database normalised to 1NF, 2NF and 3NF?

[@DatabaseStar]

Yes it should be normalised to 3NF.

[@rajatssphere8781]

Can you provide the code for this database...only just for few tables because idk how to do that.

[@DatabaseStar]

Good idea Rajat, I'll add the code (as mentioned in another comment).

[@DatabaseStar]

@Rajat - I've added a link to the description with the SQL code to create the tables. Hope that helps!

[@tylercondon3453]

Very informative video! Thanks so much!

[@DatabaseStar]

Glad it was helpful!

[@rhythmrhy1380]

Excellent job

[@DatabaseStar]

Thanks!

[@estergreis7684]

What if there is a rule that you can't loan more than two books at the same time and if you want to loan another book you have to return one of the two books you have already loaned, how can we add this in the Entity Relationship Diagram?

[@DatabaseStar]

Good question. You could try to add a constraint for loans, but I don't think a constraint could handle that kind of logic. There are a few ways you could do this: a database trigger to check the table, a function or stored procedure that does the loan process and includes this logic, or adding the logic into the application code. I would suggest either a stored procedure on the database or in the application code.

[@nadaawad9828]

Thanks 💖

[@DatabaseStar]

You’re welcome!

[@sahilsiddiqui3210]

what about the librarian , library having differnt branch

[@DatabaseStar]

Good ideas. Yes you can add those to this design.

[@azqaf4287]

Hello sir. I am sorry if i make mistake writing in english. Sir i want to add book with its author at the same time but i do not know how to do this. Can you please give me tip

[@DatabaseStar]

You can insert a record into both tables if you like.

[@tommurd3116]

Do you have dfd for this system?

[@DatabaseStar]

No not for this example

[@Mzounalqarni]

Can you make a video to make a ER diagram for this system ?

[@DatabaseStar]

What do you mean by making an ER diagram? Isn’t that what this video is?

[@Mzounalqarni]

@@DatabaseStar ER diagram showing the relationship and entities

[@DatabaseStar]

Hi Nine, this video actually shows you how to make an ER diagram for this system. At the end of the video is the final ERD, and there's a link to the ERD in the description (a PNG file)

[@PeterJoel-t6x]

How can I create my own database library

[@DatabaseStar]

What do you mean? You can follow this video as an example

[@IrfanRafiq-os3ed]

please share some queries to get data from this library database

[@DatabaseStar]

I can add some in the future.

[@carthai4590]

i'm a fan of yours

[@DatabaseStar]

Thanks!

[@ugbatmagnai3567]

Do we need create entity of library staff?

[@DatabaseStar]

If you'd like it in the database, you can create it. It could be a good enhancement if you want to capture information about library staff.

[@Bimmerish]

Hello, I have some questions how i can send a direct message to you?

[@DatabaseStar]

Hey, I have a contact form on my website that sends me an email. It’s at www.databasestar.com

[@El-Ge]

Where is the book's code which is different with book's ID, for in all libraries each books has its own book code.

[@DatabaseStar]

Good point. This can also be added to the book table as a separate field.

[@hhhh11517]

is all the ids in the diagram same.

[@DatabaseStar]

Yes I believe they are

[@theuntrainedsinger1185]

Why didn't you create a table for each copy of book? I know you didn't put it in requirements but why.

[@DatabaseStar]

Good point, and that is something I should have created, so we can track each copy of the book.

[@manyabhutani8235]

what about identifying relations?

[@DatabaseStar]

What do you mean by identifying relations?

[@Ahmad-ww4ue]

@@DatabaseStar I guess she meant relationships between the tables, i.e. parent/child or 1-to-1, one-to-many, or many-to-many.

[@ellaabbasi8723]

Why loan_fine relationship is one-to-many?

[@DatabaseStar]

It’s because there could be multiple fines for one loan. That’s just one way to design it, you could have a one to one relationship or another way.

[@banelethabede4871]

The link to SQL code is dead someone help me get the code please

[@DatabaseStar]

What happens when you click the link? I just tried it and it works. Here's the link: dbshostedfiles.s3.us-west-2.amazonaws.com/dbs/library_management_create.sql

[@banelethabede4871]

@@DatabaseStar Thank you, it kept loading after a while it gave an error saying host not available

[@neuroglide6854]

Just for the record man, the way you speak sucks....It is not loud enough, super soft and you have this disappearing end, which makes listening to you frustrating...

[@DatabaseStar]

Thanks for the feedback! Since recording this video I've worked to improve my audio quality (new microphone, better editing), so I hope the newer videos have better quality. What do you mean by "disappearing end"?