These tables hold the data that describe the library's holdings. They divide into two groups: the original Koha bibliographic data tables; and the MARC data tables that were added beginning with Koha version 2.0.
Let's look at the original Koha bibliographic data tables first:
DESCRIBE biblio; +---------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+-------------------+-------+ | biblionumber | int(11) | | PRI | 0 | | | author | text | YES | | NULL | | | title | text | YES | | NULL | | | unititle | text | YES | | NULL | | | notes | text | YES | | NULL | | | serial | tinyint(1) | YES | | NULL | | | seriestitle | text | YES | | NULL | | | copyrightdate | smallint(6) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | abstract | text | YES | | NULL | | +---------------+-------------+------+-----+-------------------+-------+ 10 rows in set
DESCRIBE biblioitems; +------------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+-------------------+-------+ | biblioitemnumber | int(11) | | PRI | 0 | | | biblionumber | int(11) | | MUL | 0 | | | volume | text | YES | | NULL | | | number | text | YES | | NULL | | | classification | varchar(25) | YES | | NULL | | | itemtype | varchar(4) | YES | | NULL | | | isbn | varchar(14) | YES | | NULL | | | issn | varchar(9) | YES | | NULL | | | dewey | varchar(10) | YES | | NULL | | | subclass | char(3) | YES | | NULL | | | publicationyear | smallint(6) | YES | | NULL | | | publishercode | varchar(255) | YES | | NULL | | | volumedate | date | YES | | NULL | | | volumeddesc | varchar(255) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | illus | varchar(255) | YES | | NULL | | | pages | varchar(255) | YES | | NULL | | | notes | text | YES | | NULL | | | size | varchar(255) | YES | | NULL | | | place | varchar(255) | YES | | NULL | | | lccn | varchar(25) | YES | | NULL | | | marc | text | YES | | NULL | | | url | varchar(255) | YES | | NULL | | +------------------+--------------+------+-----+-------------------+-------+ 23 rows in set
DESCRIBE items; +----------------------+---------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+-------------------+-------+ | itemnumber | int(11) | | PRI | 0 | | | biblionumber | int(11) | | MUL | 0 | | | multivolumepart | varchar(30) | YES | | NULL | | | biblioitemnumber | int(11) | | MUL | 0 | | | barcode | varchar(20) | YES | MUL | NULL | | | dateaccessioned | date | YES | | NULL | | | booksellerid | varchar(10) | YES | | NULL | | | homebranch | varchar(4) | YES | | NULL | | | price | decimal(8,2) | YES | | NULL | | | replacementprice | decimal(8,2) | YES | | NULL | | | replacementpricedate | date | YES | | NULL | | | datelastborrowed | date | YES | | NULL | | | datelastseen | date | YES | | NULL | | | multivolume | tinyint(1) | YES | | NULL | | | stack | tinyint(1) | YES | | NULL | | | notforloan | tinyint(1) | YES | | NULL | | | itemlost | tinyint(1) | YES | | NULL | | | wthdrawn | tinyint(1) | YES | | NULL | | | itemcallnumber | varchar(30) | YES | | NULL | | | issues | smallint(6) | YES | | NULL | | | renewals | smallint(6) | YES | | NULL | | | reserves | smallint(6) | YES | | NULL | | | restricted | tinyint(1) | YES | | NULL | | | binding | decimal(28,6) | YES | | NULL | | | itemnotes | text | YES | | NULL | | | holdingbranch | varchar(4) | YES | | NULL | | | paidfor | text | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | location | varchar(80) | YES | | NULL | | +----------------------+---------------+------+-----+-------------------+-------+ 29 rows in set
DESCRIBE biblioanalysis; +------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+-------+ | analyticaltitle | text | YES | | NULL | | | biblionumber | int(11) | | | 0 | | | analyticalauthor | text | YES | | NULL | | +------------------+---------+------+-----+---------+-------+ 3 rows in set
DESCRIBE bibliosubtitle; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | subtitle | text | | | | | | biblionumber | int(11) | | MUL | 0 | | +--------------+---------+------+-----+---------+-------+ 2 rows in set
DESCRIBE additionalauthors; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | author | text | | | | | | biblionumber | int(11) | | MUL | 0 | | +--------------+---------+------+-----+---------+-------+ 2 rows in set
DESCRIBE bibliosubject; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | subject | text | | | | | | biblionumber | int(11) | | | 0 | | +--------------+---------+------+-----+---------+-------+ 2 rows in set
DESCRIBE itemsprices; +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | itemnumber | int(11) | YES | | NULL | | | price1 | decimal(28,6) | YES | | NULL | | | price2 | decimal(28,6) | YES | | NULL | | +------------+---------------+------+-----+---------+-------+ 3 rows in set
The first three tables in this group are central to Koha's operation; even portions of the MARC data must be duplicated in these tables in order for Koha to function. The biblio table holds the basic information about the work, such as title and author; the biblioitems table holds information about the formats in which the work is expressed; and the items table holds information about the individual manifestations of these expressions. The piece of data that relates all of these tables is the biblionumber.
The marc column in the biblioitems table was used at one point in the development of MARC capabilities for Koha, but is not currently used for any MARC data.
The other five tables hold additional information about specific aspects of the work and the copies, as reflected in the name of each table and its data columns.
Three more tables hold deleted bibliographic records:
DESCRIBE deletedbiblio; +---------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+-------------------+-------+ | biblionumber | int(11) | | PRI | 0 | | | author | text | YES | | NULL | | | title | text | YES | | NULL | | | unititle | text | YES | | NULL | | | notes | text | YES | | NULL | | | serial | tinyint(1) | YES | | NULL | | | seriestitle | text | YES | | NULL | | | copyrightdate | smallint(6) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | marc | blob | YES | | NULL | | | abstract | text | YES | | NULL | | +---------------+-------------+------+-----+-------------------+-------+ 11 rows in set
DESCRIBE deletedbiblioitems; +------------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+-------------------+-------+ | biblioitemnumber | int(11) | | PRI | 0 | | | biblionumber | int(11) | | MUL | 0 | | | volume | text | YES | | NULL | | | number | text | YES | | NULL | | | classification | varchar(25) | YES | | NULL | | | itemtype | varchar(4) | YES | | NULL | | | isbn | varchar(14) | YES | | NULL | | | issn | varchar(9) | YES | | NULL | | | dewey | double(8,6) | YES | | NULL | | | subclass | char(3) | YES | | NULL | | | publicationyear | smallint(6) | YES | | NULL | | | publishercode | varchar(255) | YES | | NULL | | | volumedate | date | YES | | NULL | | | volumeddesc | varchar(255) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | illus | varchar(255) | YES | | NULL | | | pages | varchar(255) | YES | | NULL | | | notes | text | YES | | NULL | | | size | varchar(255) | YES | | NULL | | | lccn | varchar(25) | YES | | NULL | | | marc | text | YES | | NULL | | | url | varchar(255) | YES | | NULL | | +------------------+--------------+------+-----+-------------------+-------+ 22 rows in set
DESCRIBE deleteditems; +----------------------+---------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+-------------------+-------+ | itemnumber | int(11) | | PRI | 0 | | | biblionumber | int(11) | | MUL | 0 | | | multivolumepart | varchar(30) | YES | | NULL | | | biblioitemnumber | int(11) | | MUL | 0 | | | barcode | varchar(9) | | UNI | | | | dateaccessioned | date | YES | | NULL | | | booksellerid | varchar(10) | YES | | NULL | | | homebranch | varchar(4) | YES | | NULL | | | price | decimal(28,6) | YES | | NULL | | | replacementprice | decimal(28,6) | YES | | NULL | | | replacementpricedate | date | YES | | NULL | | | datelastborrowed | date | YES | | NULL | | | datelastseen | date | YES | | NULL | | | multivolume | tinyint(1) | YES | | NULL | | | stack | tinyint(1) | YES | | NULL | | | notforloan | tinyint(1) | YES | | NULL | | | itemlost | tinyint(1) | YES | | NULL | | | wthdrawn | tinyint(1) | YES | | NULL | | | bulk | varchar(30) | YES | | NULL | | | issues | smallint(6) | YES | | NULL | | | renewals | smallint(6) | YES | | NULL | | | reserves | smallint(6) | YES | | NULL | | | restricted | tinyint(1) | YES | | NULL | | | binding | decimal(28,6) | YES | | NULL | | | itemnotes | text | YES | | NULL | | | holdingbranch | varchar(4) | YES | | NULL | | | interim | tinyint(1) | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | marc | blob | YES | | NULL | | | paidfor | text | YES | | NULL | | | location | varchar(80) | YES | | NULL | | +----------------------+---------------+------+-----+-------------------+-------+ 31 rows in set
One other table related to the original, pre-MARC Koha bibliographic database is catalogueentry, which was used to cross reference subject headings; this table is not used with MARC records:
DESCRIBE catalogueentry; +----------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+-------+ | catalogueentry | text | | | | | | entrytype | char(2) | YES | | NULL | | | see | text | YES | | NULL | | | seealso | text | YES | | NULL | | | seeinstead | text | YES | | NULL | | | biblionumber | int(11) | YES | | NULL | | +----------------+---------+------+-----+---------+-------+ 6 rows in set
Finally, the websites table stores information about websites that appear in the catalogue (though this feature of Koha is not used in the MARC version):
DESCRIBE websites; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | websitenumber | int(11) | | PRI | NULL | auto_increment | | biblionumber | int(11) | | | 0 | | | title | text | YES | | NULL | | | description | text | YES | | NULL | | | url | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 5 rows in set
MARC bibliographic records are stored primarily in one table:
DESCRIBE marc_subfield_table; +---------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+----------------+ | subfieldid | bigint(20) unsigned | | PRI | NULL | auto_increment | | bibid | bigint(20) unsigned | | MUL | 0 | | | tag | char(3) | | MUL | | | | tagorder | tinyint(4) | | MUL | 1 | | | tag_indicator | char(2) | | MUL | | | | subfieldcode | char(1) | | MUL | | | | subfieldorder | tinyint(4) | | MUL | 1 | | | subfieldvalue | varchar(255) | YES | MUL | NULL | | | valuebloblink | bigint(20) | YES | | NULL | | +---------------+---------------------+------+-----+---------+----------------+ 9 rows in set
The marc_subfield_table table holds the value of each MARC subfield and relates this data to other subfields of a record through the tag, subfieldcode, and tagorder. Using these three data pieces, Koha can reconstruct a MARC record from a group of subfields selected from this table.
If the subfieldvalue is longer than 255 characters (and thus too big to fit in this table), then a value is set in the valuebloblink column that refers to a data row in the marc_blob_subfield table, where the long subfield value is stored:
DESCRIBE marc_blob_subfield; +---------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------+------+-----+---------+----------------+ | blobidlink | bigint(20) | | PRI | NULL | auto_increment | | subfieldvalue | longtext | | | | | +---------------+------------+------+-----+---------+----------------+ 2 rows in set
Subfields values are also broken into single words and stored in a separate table that is used when performing keyword searches:
DESCRIBE marc_word; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | bibid | bigint(20) | | MUL | 0 | | | tagsubfield | varchar(4) | | MUL | | | | tagorder | tinyint(4) | | MUL | 1 | | | subfieldorder | tinyint(4) | | MUL | 1 | | | word | varchar(255) | | MUL | | | | sndx_word | varchar(255) | | MUL | | | +---------------+--------------+------+-----+---------+-------+ 6 rows in set
The sndx_word column stores syndetic values for the word, so that Koha can find words that sound like a search term.
MARC records which have been retrieved through a Z3950 search or uploaded into the MARC reservoir, but have not yet been added to the catalogue, are stored in either the marc_breeding or the uploadedmarc tables, particularly in the marc column (in ISO2709 format):
DESCRIBE marc_breeding; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | | PRI | NULL | auto_increment | | file | varchar(80) | | | | | | isbn | varchar(10) | | MUL | | | | title | varchar(128) | YES | MUL | NULL | | | author | varchar(80) | YES | | NULL | | | marc | text | | | | | | encoding | varchar(40) | | | | | | z3950random | varchar(40) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 8 rows in set
DESCRIBE uploadedmarc; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | marc | longblob | YES | | NULL | | | hidden | smallint(6) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | +--------+--------------+------+-----+---------+----------------+ 4 rows in set
The table which relates the MARC data to the original Koha bibliographic tables is marc_biblio:
DESCRIBE marc_biblio; +---------------+---------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+------------+----------------+ | bibid | bigint(20) unsigned | | PRI | NULL | auto_increment | | biblionumber | int(11) | | MUL | 0 | | | datecreated | date | | | 0000-00-00 | | | datemodified | date | YES | | NULL | | | origincode | char(20) | YES | MUL | NULL | | | frameworkcode | char(4) | | | | | +---------------+---------------------+------+-----+------------+----------------+ 6 rows in set
Finally, the marcrecorddone table does not seem to be used for anything.
MARC authority records are stored in a group of tables which partly correspond to the MARC bibliographic tables:
DESCRIBE auth_subfield_table; +---------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+----------------+ | subfieldid | bigint(20) unsigned | | PRI | NULL | auto_increment | | authid | bigint(20) unsigned | | MUL | 0 | | | tag | char(3) | | MUL | | | | tagorder | tinyint(4) | | | 1 | | | tag_indicator | char(2) | | | | | | subfieldcode | char(1) | | MUL | | | | subfieldorder | tinyint(4) | | | 1 | | | subfieldvalue | varchar(255) | YES | MUL | NULL | | +---------------+---------------------+------+-----+---------+----------------+ 8 rows in set
DESCRIBE auth_word; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | authid | bigint(20) | | MUL | 0 | | | tagsubfield | varchar(4) | | MUL | | | | tagorder | tinyint(4) | | | 1 | | | subfieldorder | tinyint(4) | | | 1 | | | word | varchar(255) | | MUL | | | | sndx_word | varchar(255) | | MUL | | | +---------------+--------------+------+-----+---------+-------+ 6 rows in set
DESCRIBE auth_header; +--------------+---------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+------------+----------------+ | authid | bigint(20) unsigned | | PRI | NULL | auto_increment | | authtypecode | char(10) | | | | | | datecreated | date | | | 0000-00-00 | | | datemodified | date | YES | | NULL | | | origincode | char(20) | YES | MUL | NULL | | +--------------+---------------------+------+-----+------------+----------------+ 5 rows in set
DESCRIBE bibliothesaurus; +-----------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------+------+-----+---------+----------------+ | id | bigint(20) | | PRI | NULL | auto_increment | | freelib | char(255) | | MUL | | | | stdlib | char(255) | | MUL | | | | category | char(10) | | MUL | | | | level | tinyint(4) | | | 1 | | | hierarchy | char(80) | | MUL | | | | father | char(80) | | | | | +-----------+------------+------+-----+---------+----------------+ 7 rows in set
The auth_header and bibliothesaurus tables hold some basic information about authority types that the Koha user has defined and how a subject authority record relates to others. The auth_subfield_table and auth_word tables hold the same type of data as their similarly-named MARC bibliographic tables.
Finally, two other tables hold data used to hold data about periodicals:
DESCRIBE subscription; +-----------------+--------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+------------+----------------+ | biblionumber | int(11) | | | 0 | | | subscriptionid | int(11) | | PRI | NULL | auto_increment | | librarian | varchar(100) | YES | | | | | startdate | date | YES | | 0000-00-00 | | | aqbooksellerid | int(11) | YES | | 0 | | | cost | int(11) | YES | | 0 | | | aqbudgetid | int(11) | YES | | 0 | | | weeklength | tinyint(4) | YES | | 0 | | | monthlength | tinyint(4) | YES | | 0 | | | numberlength | tinyint(4) | YES | | 0 | | | periodicity | tinyint(4) | YES | | 0 | | | dow | varchar(100) | YES | | | | | numberingmethod | varchar(100) | YES | | | | | notes | text | YES | | NULL | | | status | varchar(100) | | | | | | add1 | int(11) | YES | | 0 | | | every1 | int(11) | YES | | 0 | | | whenmorethan1 | int(11) | YES | | 0 | | | setto1 | int(11) | YES | | NULL | | | lastvalue1 | int(11) | YES | | NULL | | | add2 | int(11) | YES | | 0 | | | every2 | int(11) | YES | | 0 | | | whenmorethan2 | int(11) | YES | | 0 | | | setto2 | int(11) | YES | | NULL | | | lastvalue2 | int(11) | YES | | NULL | | | add3 | int(11) | YES | | 0 | | | every3 | int(11) | YES | | 0 | | | innerloop1 | int(11) | YES | | 0 | | | innerloop2 | int(11) | YES | | 0 | | | innerloop3 | int(11) | YES | | 0 | | | whenmorethan3 | int(11) | YES | | 0 | | | setto3 | int(11) | YES | | NULL | | | lastvalue3 | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+------------+----------------+ 33 rows in set
DESCRIBE serial; +----------------+--------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+------------+----------------+ | serialid | int(11) | | PRI | NULL | auto_increment | | biblionumber | varchar(100) | | | | | | subscriptionid | varchar(100) | | | | | | serialseq | varchar(100) | | | | | | status | tinyint(4) | | | 0 | | | planneddate | date | | | 0000-00-00 | | | notes | varchar(255) | YES | | | | +----------------+--------------+------+-----+------------+----------------+ 7 rows in set
The subscription table holds general information about a periodicals subscription, while the serial table holds information about individual issues of a periodical.