In the homework given in the database course, I have to do a database design for a library management system. There are 2 separate parts in the project description that I do not understand.
First, we have different user types called library manager (lm) and regular user (ru). These two types of users also have different features. For example, ru.s can only see their own borrow information, while lm.s can see the borrow information of all users. Furthermore, ru.s do not have the authority to add books to the system, while lm.s can add new books to the system. My first question is about whether the properties related to the authorization I just listed can be kept in the database. Or will these authorization features be features that need to be created and added to different pages for each user type while implementing the website? If these are to be determined on the website, how can I be sure that no ru has sent unauthorized queries?
The second part I do not understand is that while keeping the borrow information of the ru.s, do I need to keep three separate information such as "date of books were borrowed", "date of books should be returned" and "date of the book was brought back"? Because the borrowing period of each book is fixed and set as 1 month. So it seems enough for me to only record the date the book was borrowed. Because by adding 1 month to this date, the date of returning the book can already be calculated.
Besides, even though I think I should keep a record of the date that the book was brought back, this information will be null when the user just borrows a book and not returns it yet. And as far as I know, keeping null parts in the database means bad design. So I could not understand how to fix these problems, thanks in advance for your help.
Yes, you can keep properties needed for authorization in the database, or you could use functionality implemented elsewhere like in a proxy server, web server, or existing libraries (like pam etc). These are design choices, and there may be trade-offs.
Yes, I would not store redundant information if you are studying database modeling. That said, what would happen if that fixed 1 month is changed to 2 months while you may have some books outstanding?
An entirely different way of modeling this would be to record events that happen. (today, user a, borrowed, book b), (last friday, user a, returned, book c) etc. Then you build views, or materialized tables that reflect how you want to view the data. This is referred to as event sourcing, and if you split your read and write part of application the command and query responsibility segregation pattern.