These tables hold the data recording the library's transactions. They generally cross-reference to borrower and catalogue data tables.
Perhaps the most important of these is the issues table, which holds information about which items have been loaned to which borrowers:
DESCRIBE issues; +-----------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | | MUL | 0 | | | itemnumber | int(11) | | MUL | 0 | | | date_due | date | YES | | NULL | | | branchcode | char(4) | YES | | NULL | | | issuingbranch | char(18) | YES | | NULL | | | returndate | date | YES | | NULL | | | lastreneweddate | date | YES | | NULL | | | return | char(4) | YES | | NULL | | | renewals | tinyint(4) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | +-----------------+------------+------+-----+-------------------+-------+ 10 rows in set
Notice the timestamp data in this table; this type of data is fairly common in the transaction tables, allowing the library staff to determine the date and time when a transaction took place.
A statistics table holds summary data about issues; data in this table is more dependable than data in the issues table, which may be affected by other activity:
DESCRIBE statistics; +----------------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------------------+-------+ | datetime | datetime | | MUL | 0000-00-00 00:00:00 | | | branch | varchar(4) | YES | | NULL | | | proccode | varchar(4) | YES | | NULL | | | value | double(16,4) | YES | | NULL | | | type | varchar(16) | YES | | NULL | | | other | text | YES | | NULL | | | usercode | varchar(10) | YES | | NULL | | | itemnumber | int(11) | YES | | NULL | | | itemtype | varchar(4) | YES | | NULL | | | borrowernumber | int(11) | YES | | NULL | | +----------------+--------------+------+-----+---------------------+-------+ 10 rows in set
Two tables related to issues keep track of reserves on library materials:
DESCRIBE reserves; +------------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | | | 0 | | | reservedate | date | | | 0000-00-00 | | | biblionumber | int(11) | | | 0 | | | constrainttype | char(1) | YES | | NULL | | | branchcode | varchar(4) | YES | | NULL | | | notificationdate | date | YES | | NULL | | | reminderdate | date | YES | | NULL | | | cancellationdate | date | YES | | NULL | | | reservenotes | text | YES | | NULL | | | priority | smallint(6) | YES | | NULL | | | found | char(1) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | itemnumber | int(11) | YES | | NULL | | +------------------+-------------+------+-----+-------------------+-------+ 13 rows in set
DESCRIBE reserveconstraints; +------------------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-----------+------+-----+-------------------+-------+ | borrowernumber | int(11) | | | 0 | | | reservedate | date | | | 0000-00-00 | | | biblionumber | int(11) | | | 0 | | | biblioitemnumber | int(11) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | +------------------+-----------+------+-----+-------------------+-------+ 5 rows in set
The reserveconstraints table allows for options like delayed reserves.
Another table holds data about movement of items between library branches:
DESCRIBE branchtransfers; +-------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+-------+ | itemnumber | int(11) | | | 0 | | | datesent | datetime | YES | | NULL | | | frombranch | varchar(4) | YES | | NULL | | | datearrived | datetime | YES | | NULL | | | tobranch | varchar(4) | YES | | NULL | | | comments | text | YES | | NULL | | +-------------+------------+------+-----+---------+-------+ 6 rows in set
Two tables hold data about fines and other charges owed by borrowers:
DESCRIBE accountlines; +-------------------+---------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | | MUL | 0 | | | accountno | smallint(6) | | | 0 | | | itemnumber | int(11) | YES | | NULL | | | date | date | YES | | NULL | | | amount | decimal(28,6) | YES | | NULL | | | description | text | YES | | NULL | | | dispute | text | YES | | NULL | | | accounttype | varchar(5) | YES | | NULL | | | amountoutstanding | decimal(28,6) | YES | | NULL | | | timestamp | timestamp | YES | MUL | CURRENT_TIMESTAMP | | +-------------------+---------------+------+-----+-------------------+-------+ 10 rows in set
DESCRIBE accountoffsets; +----------------+---------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+-------------------+-------+ | borrowernumber | int(11) | | | 0 | | | accountno | smallint(6) | | | 0 | | | offsetaccount | smallint(6) | | | 0 | | | offsetamount | decimal(28,6) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | +----------------+---------------+------+-----+-------------------+-------+ 5 rows in set
The accountlines table holds information about the original charge, while accountoffsets holds data about payments toward the charge.
Two tables hold data from Z3950 searches performed by the catalogers:
DESCRIBE z3950queue; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | term | text | YES | | NULL | | | type | varchar(10) | YES | | NULL | | | startdate | int(11) | YES | | NULL | | | enddate | int(11) | YES | | NULL | | | done | smallint(6) | YES | | NULL | | | results | longblob | YES | | NULL | | | numrecords | int(11) | YES | | NULL | | | servers | text | YES | | NULL | | | identifier | varchar(30) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 10 rows in set
DESCRIBE z3950results; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | queryid | int(11) | YES | MUL | NULL | | | server | varchar(255) | YES | | NULL | | | startdate | int(11) | YES | | NULL | | | enddate | int(11) | YES | | NULL | | | results | longblob | YES | | NULL | | | numrecords | int(11) | YES | | NULL | | | numdownloaded | int(11) | YES | | NULL | | | highestseen | int(11) | YES | | NULL | | | active | smallint(6) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 10 rows in set
Five tables contain the data related to orders for library materials:
DESCRIBE suggestions; +-----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+-------------------+----------------+ | suggestionid | int(8) | | PRI | NULL | auto_increment | | suggestedby | int(11) | | MUL | 0 | | | managedby | int(11) | YES | MUL | NULL | | | STATUS | varchar(10) | | | | | | note | text | YES | | NULL | | | author | varchar(80) | YES | | NULL | | | title | varchar(80) | YES | | NULL | | | copyrightdate | smallint(6) | YES | | NULL | | | publishercode | varchar(255) | YES | | NULL | | | date | timestamp | YES | | CURRENT_TIMESTAMP | | | volumedesc | varchar(255) | YES | | NULL | | | publicationyear | smallint(6) | YES | | 0 | | | place | varchar(255) | YES | | NULL | | | isbn | varchar(10) | YES | | NULL | | | mailoverseeing | smallint(1) | YES | | 0 | | | biblionumber | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+-------------------+----------------+ 16 rows in set
DESCRIBE aqbasket; +-------------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+-------------+------+-----+---------+----------------+ | basketno | int(11) | | PRI | NULL | auto_increment | | creationdate | date | YES | | NULL | | | closedate | date | YES | | NULL | | | booksellerid | varchar(10) | YES | | NULL | | | authorisedby | varchar(10) | YES | | NULL | | | booksellerinvoicenumber | text | YES | | NULL | | +-------------------------+-------------+------+-----+---------+----------------+ 6 rows in set
DESCRIBE aqorders; +-------------------------+---------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+---------------+------+-----+-------------------+----------------+ | ordernumber | int(11) | | PRI | NULL | auto_increment | | biblionumber | int(11) | YES | | NULL | | | title | text | YES | | NULL | | | entrydate | date | YES | | NULL | | | quantity | smallint(6) | YES | | NULL | | | currency | char(3) | YES | | NULL | | | listprice | decimal(28,6) | YES | | NULL | | | totalamount | decimal(28,6) | YES | | NULL | | | datereceived | date | YES | | NULL | | | booksellerinvoicenumber | text | YES | | NULL | | | freight | decimal(28,6) | YES | | NULL | | | unitprice | decimal(28,6) | YES | | NULL | | | quantityreceived | smallint(6) | YES | | NULL | | | cancelledby | varchar(10) | YES | | NULL | | | datecancellationprinted | date | YES | | NULL | | | notes | text | YES | | NULL | | | supplierreference | text | YES | | NULL | | | purchaseordernumber | text | YES | | NULL | | | subscription | tinyint(1) | YES | | NULL | | | serialid | varchar(30) | YES | | NULL | | | basketno | int(11) | YES | | NULL | | | biblioitemnumber | int(11) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | rrp | decimal(13,2) | YES | | NULL | | | ecost | decimal(13,2) | YES | | NULL | | | gst | decimal(13,2) | YES | | NULL | | | budgetdate | date | YES | | NULL | | | sort1 | varchar(80) | YES | | NULL | | | sort2 | varchar(80) | YES | | NULL | | +-------------------------+---------------+------+-----+-------------------+----------------+ 29 rows in set
DESCRIBE aqorderbreakdown; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | ordernumber | int(11) | YES | | NULL | | | linenumber | int(11) | YES | | NULL | | | branchcode | char(4) | YES | | NULL | | | bookfundid | char(5) | | | | | | allocation | smallint(6) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 5 rows in set
DESCRIBE aqorderdelivery; +------------------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+------------+-------+ | ordernumber | date | | | 0000-00-00 | | | deliverynumber | smallint(6) | | | 0 | | | deliverydate | varchar(18) | YES | | NULL | | | qtydelivered | smallint(6) | YES | | NULL | | | deliverycomments | text | YES | | NULL | | +------------------+-------------+------+-----+------------+-------+ 5 rows in set
One table holds information about periodicals:
DESCRIBE subscriptionhistory; +----------------+--------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+------------+-------+ | biblionumber | int(11) | | MUL | 0 | | | subscriptionid | int(11) | | PRI | 0 | | | histstartdate | date | | | 0000-00-00 | | | enddate | date | YES | | 0000-00-00 | | | missinglist | longtext | | | | | | recievedlist | longtext | | | | | | opacnote | varchar(150) | | | | | | librariannote | varchar(150) | | | | | +----------------+--------------+------+-----+------------+-------+ 8 rows in set
Finally, two tables hold information about logged-in users:
DESCRIBE sessions; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | sessionID | varchar(255) | | PRI | | | | userid | varchar(255) | YES | | NULL | | | ip | varchar(16) | YES | | NULL | | | lasttime | int(11) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 4 rows in set
DESCRIBE sessionqueries; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | sessionID | varchar(255) | | | | | | userid | varchar(100) | | | | | | ip | varchar(18) | | | | | | url | text | | | | | +-----------+--------------+------+-----+---------+-------+ 4 rows in set
The sessionqueries table does not seem to be currently used by Koha.