Item Table

The item table holds the library title item information. A library title can have any number of copies (items). Information that can differ among copies of a title is stored in the item table. The borrower requests library materials by title, but borrows an item. The item table diagram shows the column properties of the table.

• The item# column is the primary key for an item. The Identity attribute guarantees that a unique number will be generated for each new item added to the table. The identity seed and identity increment are 1. The first item# to be assigned is 1, and each subsequently added item# will be incremented by 1.
• The barcode column holds the bar code for each item, or rather the numeric representation or uniform product code (UPC) of the bar code. Bar codes are unique for each item and are used when checking items back into the library. We could have used the bar code as the primary key on the item table.
• The bib# column is the foreign key that links the item table to the title table. The item table represents the many and the title table the one in this one-to-many relationship. The bib# is not an identity column in the item table. The bib# is required and the value is verified by a bib# in the title table. It would not make sense to allow a record in the item table that did not reference a record in the title table.
• The location column holds the library location of the item, not the location of the borrower.
• The source column holds the origin of the item.
• The price column holds the price of the item. The data type is money and the default price is 0.
• The notes column holds any miscellaneous information for the item.
• The staff_only column identifies items that are only available to staff. This is a Boolean field. The default value is 0 or False.
• The item_status column holds the status of the item. The item status codes are identified in the item_status table. All item_status entries in the item table are verified to exist in the item_status table.
• The last_cko_date column is the last date this item was checked out. The data type is datetime.
• The n_ckos column holds the count of how many times the item has been checked out. The default value is 0.
• The borrower# column is the foreign key that links the item table to the borrower table; it represents the second one-to-many relationship in which the item table participates. This is how we track who has each copy of a title checked out.
• The due_date column is the due date for the library item.
• The n_renewals column holds the count of how many times a borrower has renewed the item. The default value is 0.