Conceptually, it doesn't require a great leap to design a system that notifies someone via email when a particular event occurs in the database. Let's take a close look at how we can use such a feature in the library system, starting with a look at the underlying database structures.
We do have to introduce two more tables into the design, because so far, our library has books and borrowers, but no transaction records. We can create a simple table to hold transaction information; that is, it correlates a borrower, a particular copy of a book, an event (like check-in or check-out), and a date:
CREATE TABLE user_book_copy_events ( barcode_id VARCHAR2(100) NOT NULL REFERENCES book_copies (barcode_id), borrower_id NUMBER NOT NULL REFERENCES lib_users (id), event_name VARCHAR2(30) NOT NULL CHECK (event_name IN ('checkin','checkout')), timestamp DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT user_book_events_pk PRIMARY KEY (barcode_id, borrower_id, event_name, timestamp) );
The primary key is made up of all four columns because:
Each borrower can check out many books.
Over time, a book will be repeatedly checked in or out.
A given user might check a book out more than once.
As designed, the system prevents two borrowers from performing the exact operation on the same copy of a book at the same time.
This table cannot store information about book-reserving events, because books should be reserved ...