The database must be fast for the looking up of records. It must also be flexible. The database will be organized in the following fashion:
- There must be one table for each type of object in the library. The type and owning library must be able to be determined from the object's id. Because we know the owning library and type of the object, this information may be omitted from the table for the type of object. This allows the id stored in the table to be an integer type.
- The loans table keeps track of what borrowers have what items out on loans. The loans table must contain at least the full ids of the borrower and item as well as the date the item is due. The ids must be the full string id which include type and library information because the library needs to know what type the item is and where the borrower's record is.
- The loanPeriods table keeps track of two things: default checkout periods for item types and special case items. The special case items are stored by full string id and the default for types are by the table name for that type. The loan period should be stored as an SQL time type, but is currently (3/30/1999) stored as milliseconds.
- The info table is a key/value table that stores various information about the library such as its name, registered object types, etc.