Correct relations to Loans table?

Question

Hello stackers!

Ive made a library databse, i was wondering.. i am making one-to-one relation between Copies and Loans. and one-to-many relation from Users to Loans.

Since a copy of a book only should be allowed to be assigned one loan at a time, and a loan should only be able to containt one copy of a book. if they rent other books, its multiple loans.

and a user should be able to make multiple loans, but a loan can only be assigned one specific user.

is my current relations between these three tables correct? if not, i would love to know how to fix it, and the reason to my failed logic on the issue.

thank you in advance!

looks good to me , however to bullet proof your logic you may need a conditional unique index on Loans table — eshirvana, Oct 09 '20 at 20:33
It is wrong - and you haven't really changed anything from your prior question as regard to relationships. But you should have some basic use cases to evaluate your design. One of those should be something like: User x checks out book y and returns it. User Y checks out this same book the day after it was returned. Does you design support this? Apply that same process to generate your use cases and validate all the other relationships. — SMor, Oct 09 '20 at 20:33
And I use the term "wrong" because that is one of the primary purposes of a library - to lend the same book over and over and over. As time passes, there are many other things that must be accommodated for this system to work. Books get lost, are sold off, removed from circulation, etc. A loan is not returned on time. A loan is extended. And books are not the only things that libraries lend. Some things they don't lend at all - like periodicals. These are obvious complications of the basic design but no one knows what your ultimate goal is. — SMor, Oct 09 '20 at 20:37

score 0 · Answer 1 · answered Oct 09 '20 at 20:38

0

The model looks good to me. You may need to apply some in the logic to enforce a user to have only one loan with same copy of a book.

A user will be able loan a copy over and over again ? then the relationship to loan to copy 1:M

answered Oct 09 '20 at 20:38

rens

43
6

Well.. he will be able to loan it.. but if he wants to reloan it, he will have to do so after status changes from loaned to not loaned.. i mean, then he has to loan it once again.. but that makes it a different loan i guess? also by that, i will be able to track how many loans he has made, even if its to the same copy of a book? – Oct 09 '20 at 20:48

score 0 · Accepted Answer · answered Oct 09 '20 at 20:55

Following on from the earlier answer. If you add a User_Roles table it could/(will) prevent you from falling into the membership trap. If you assume a user with the Admin role can perform every function a user with only Basic role, then every function which requires role-checking has to have a list of acceptable roles (Admin + Basic). Many times it is more efficient to just directly assign all the different roles, i.e. Basic AND Admin, to individual users. Then when a feature requires Basic role-authorization all users can treated the same way. It's simpler in the long run.

The Loans table has a number of issues. First, there's no primary key, to be consistent with the rest of your design, it could be a LoanID. CopyID should a foreign key to the Copies table (maybe that's what is currently drawn).

One 'advanced' or modern approach to your data model could be to use a temporal table to model the Copies. Since a single copy may only be lent out 1 time, properties of the loan could be add to the Copies table. Then anytime a change is made the System Version'ed Copies table the Copies_history table would automatically keep a full accounting of all prior loan activity.

Correct relations to Loans table?

2 Answers2