SQL Structure

presented by Paul Poulain

authorised_values table used to define lists available in a lot of places. Paul, "the swiss knife tool in Koha"

Don't try to fill the biblios and items tables directly, use bulkmarcimport.pl tool or the API (more complex).

Koha is setup behind the scenes to handle both UNIMARC and MARC21. Lots of data in MARC21, very few important and very frequently used like title, author, call no.

History

• Koha 1.x was not dealing with MARC.
• Koha 1.x implemented something like FRBR before FRBR existed.
• Koha 2.0 implemented MARC but relied on the previous 3 tables

Biblio and Biblioitems tables
Raw MARC data is stored in biblioitems table in the marcxml field. Some information is stored in a clearly named field in biblio table. The biblioitems table that contains information that is the root information of the edition.

In Koha 3.0 you should have a 1 to 1 correlation between the biblio and biblioitems table. The two tables could be merged into one, but has not been implemented yet. Koha takes care of translating the MARC database into the clearly named field, that is why you must use the import tool or the API.

The biblioitems table should be called biblioeditions, but no developer has renamed it yet.

Which table is used..

• On results lists, MARC detail from marcxml
• On standard biblio detail screen, if you have XSLT the data is taken from marcxml (?)
  
• For UNIMARC detail screens, the biblio and biblioitems tables are used.
  
• If just a few key fields, it is grabbed from biblio and biblioitems tables

If you crash Zebra database, everything is stored in the MySQL table.

Items table contains a row for each representation of the physical item in the library. All information is contained in the biblioitems.marcxml field, however, just like biblio table contains decoded information, the items table also contains some specific decoded fields.

In Koha the primary key for biblio data is the biblio#.

Borrower table
Surname is the only mandatory field in the borrower table. Personal information, first name, title, other names, intials, dateofbirth, address, street number, street type, address, address2, zip code, city , phone, email are all examples of fields in the borrower table.

The primary key for borrower table is the borrower#. The password in Koha is never stored in plain text and only stored in the md5 hash form.

Some free to use fields are sort1 and sort2, linked to authorised_valules. Borrower_attributes table supports extended attributes.

Issues table (checkouts/checkins)
All current issues are stored in issues table. All previous/finished issues are stored in old_issues table. They both have the same structure.

Four important fields, borrowernumber, itemnumber, date_due, issuedate