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:
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}.
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).
Copyright © 2002–2006, by Tom Jewett. Links to this site are welcome and encouraged. Individual copies may be printed for non-commercial classroom or personal use; however, this material may not be reposted to other web sites or newsgroups, or included in any printed or electronic publication, whether modified or not, without specific permission from the author.