UML Design

Other Tech Topics
1

I'm just starting out learning databases with SQLServer as the backend, but I've got some very basic UML questions.

In regards to a Library database, how would you properly show the relation between a User and the Items they borrow?

Say 1 user, at any one time, can borrow up to 3 items, is this correct?
Or in the UML diagram, would the BorrowTable be shown as an Association, like the latter example?

image
image.png959×520 13.3 KB

In the BorrowTable, would it require it's own Primary Key like I have there, or would the combination of the foreign keys UserID + Barcode be enough to form a unique key? Though what happens if a user re-borrows the same book (or do we typically wipe this kind of BorrowTable association/intermediary upon returning the books)?
Maybe a history table of borrowed items would form another table elsewhere, either off the User table, or off the CatalogItems table that keeps a record of which users have borrows x item.

Sorry, exceptionally new to all this. Happy and eager to read any documentation you want to throw my way.

Thanks!

2

Great job for a new learner! If you add BorrowDate to UserId and BarCode to the UniqueKey youbwill be good to go

3

Unless they read it super fast and return same date and check it right back out in which case BorrowDate should have time value :flushed::grin:

4

Yes, the BorrowTable is an Association (which becomes a "junction" table in the physical db design).

The key should be ( USerID, Barcode, LoanDate ). If that's not unique, then (a) either consider all checkouts and checkins on the same day to "merge" (probably not a good design) or (b) add some type of numeric value to make it unique (a checkout count -- 1 for the first time per day, 2 for the second time, etc. -- or just a sequential number counter (which would become an identity in SQL Server, autonumber in Access, etc.).