Design pattern: many-to-many with history (the library loan)

Remember that the UML association class represents the attributes of a many-to-many association, but can only be used if there is at most one pairing of any two individuals in the relationship. This means, for example in order entry, that there can be only one order line for each item ordered. This constraint is consistent with the enterprise being modeled.

• There are times when we need to allow the same two individuals in a many-to-many association to be paired more than once. This frequently happens when we need to keep a history of events over time.

Example: In a library, customers can borrow many books and each book can be borrowed by many customers, so this seems to be a simple many-to-many association between customers and books. But any one customer may borrow a book, return it, and then borrow the same book again at a later time. The library records each book loan separately. There is no invoice for each set of borrowed books and therefore no equivalent here of the Order in the order entry example. (You have already seen other parts of the library model in exercises.)

• The loan is an event that happens in the real world; we need a regular class to model it correctly. We’ll call this the “library loan” design pattern. First, we need to understand what the classes and associations mean:

“A customer is any person who has registered with the library and is elegible to check out books.”

“A catalog entry is essentially the same as an old-fashioned index card that represents the title and other information about books in the library, and allows the customers to quickly find a book on the shelves.”

“A book-on-the-shelf is the physical volume that is either sitting on the library shelves or is checked out by a customer. There can be many physical books represented by any one catalog entry.”

“A loan event happens when one customer takes one book to the checkout counter, has the book and her library card scanned, and then takes the book home to read.”

“Each Customer makes zero or more Loans.”

“Each Loan is made by one and only one Customer.”

“Each Loan checks out one and only one BookOnShelf.”

“Each BookOnShelf is checked out by zero or more Loans.”

“Each BookOnShelf is represented by one and only one CatalogEntry (catalog card).”

“Each CatalogEntry can represent one or more physical copies of the same book-on-the-shelf.”

Class diagram

Library loan class diagram

Other views of this diagram: Large image - Data dictionary (text)

Relation scheme diagram

As in the order entry example, the Customers table will need a surrogate key (added by us) to save space when it is copied in the Loans. The CatalogEntries scheme already has two external keys: the call number and the ISBN (International Standard Book Number). The first of these is defined by the Library of Congress Classification system, and contains codes that represent the subject, author, and year published. The second of these is defined by an ISO (International Standards Organization) standard, number 2108. We’ll use the callNmbr as the primary key, since it has more descriptive value than the ISBN and is smaller than the descriptive CK {title, pubDate}.

Library loan relation scheme

Other views of this diagram: Large image - Data dictionary (text)

• As we would do in a junction table scheme, we’ll copy the primary key attributes from both the Customers and the BooksOnShelf into the Loans scheme. This tells us which customer borrowed which book, but it doesn't tell when it was borrowed; we have to know the dateTimeOut in order to pair a customer with the same book more than once. We can call this a discriminator attribute, since it allows us to discriminate between the multiple pairings of customer and book. If you refer back to the UML class diagram, you’ll see that the loan, which would have been a many-to-many association class between customers and books, has become a “real” class because of the discriminator attribute.

• In most cases like this, we would use both FKs plus the discriminator attribute dateTimeOut as PK of the Loans; here we need only the FK from the BooksOnShelf and the dateTimeOut (since it is physically impossible to run the same book through the scanner more than once at a time). Notice that there is actually another CK for loans: {dateTimeOut, scannerID}, since it is also physically impossible for the same scanner to read two different books at exactly the same time. We chose {callNmbr, copyNmbr, dateTimeOut} because it has just a bit more descriptive value and because we don’t care about size here (since the Loan has no children).