[Koha] List of what each field in the various tables do
Stephen Hedges
shedges at skemotah.com
Mon Nov 17 06:56:04 NZDT 2003
Freddie Cash said:
> We're just starting out with Koha 2.0 to see if it could be used as a
replacement for Follett. However, we are running into a few snags that
should be fairly simple to fix.
>
> Our major snag is lack of documentation for the internals of Koha. For
instance, is there a description of the various database tables, and what
each field is used for? This would make mapping fields to MARC fields
much simpler. We're running into problems with our mappings, especially
with the distinction between items and biblioitems, for instance.
>
> Is this kind of low-level documentation available anywhere??
Freddie, I don't know of any nice, neat documentation for what you want.
There is an old mapping of the relationships of the various tables at
http://irref.mine.nu/user/dchud/koha-schema/branches-issues-borrowers-with-datatypes.jpg,
but as I said, it is old and some things have changes a little.
For the most part, the MARC records load themselves by using
bulkmarcimport.pl, so long as you are able to dump your MARC records in
the standard iso2709 MARC format. There are still a few bugs left in this
process, but by and large it will put things where you want them without
worrying too much about what each column in the bibliographic tables does.
Patrons is another matter. I've been working on a document about
migrating data from a MARC-based ILS to Koha 2.0, which I hope to post
eventually. The part about migrating patron data is close enough to done
to share with you here -- so long as you remember that I have not yet had
a chance to ask the developers to proof-read it, so they may have some
corrections to submit. Here it is:
quote(
User Information
Unlike bibliographic records, there is as yet no commonly used standard
for user records, though the relatively new NCIP standard addresses some
aspects of user information and may become as widespread as MARC records
someday. Meanwhile, this is an area where you will have to dig out as
much information as possible from your existing database, using whatever
means possible. If you are fortunate enough to have some sort of export
utility with you current ILS, you just need to determine that it exports
all the data you want. Most likely, however, you have no such utility and
will have to either access your database directly and dump the data you
need (if possible) or ask your current vendor to do it for you. In any
case, you will want to retrieve the following information about your
users:
* names (first names, last name, and perhaps title);
* library card number;
* user ID (if different from library card number);
* street address;
* mailing address, or any other alternate address you might have on
file;
* date of birth (if available);
* telephone number;
* user category code (e.g. 'A' for adult);
* date enrolled (if available);
* branch where enrolled (if available);
* any status codes (e.g. blocked, bad address, etc.);
* user password (if available in plain text);
* preferred method of contact (if available);
* any notes.
This information will then need to be loaded into the Koha borrowers
table. The columns in this table are a little confusing, since the table
has been expanded at various times to accomodate the needs of libraries
needing to store more information and therefore needing more columns in
the table. Here is the current table structure, with some notes on what
the columns can contain:
SHOW COLUMNS FROM borrowers;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+------------------+--------------+------+-----+---------+----------------+
| borrowernumber | int(11) | | MUL | NULL | auto_increment
|
| cardnumber | varchar(9) | | MUL | | |
| surname | text | | | | |
| firstname | text | | | | |
| title | text | YES | | NULL | |
| othernames | text | YES | | NULL | |
| initials | text | | | | |
| streetaddress | text | | | | |
| suburb | text | YES | | NULL | |
| city | text | | | | |
| phone | text | | | | |
| emailaddress | text | YES | | NULL | |
| faxnumber | text | YES | | NULL | |
| textmessaging | text | YES | | NULL | |
| altstreetaddress | text | YES | | NULL | |
| altsuburb | text | YES | | NULL | |
| altcity | text | YES | | NULL | |
| altphone | text | YES | | NULL | |
| dateofbirth | date | YES | | NULL | |
| branchcode | varchar(4) | | | | |
| categorycode | char(2) | YES | | NULL | |
| dateenrolled | date | YES | | NULL | |
| gonenoaddress | tinyint(1) | YES | | NULL | |
| lost | tinyint(1) | YES | | NULL | |
| debarred | tinyint(1) | YES | | NULL | |
| studentnumber | text | YES | | NULL | |
| school | text | YES | | NULL | |
| contactname | text | YES | | NULL | |
| borrowernotes | text | YES | | NULL | |
| guarantor | int(11) | YES | | NULL | |
| area | char(2) | YES | | NULL | |
| ethnicity | varchar(50) | YES | | NULL | |
| ethnotes | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| expiry | date | YES | | NULL | |
| altnotes | varchar(255) | YES | | NULL | |
| altrelationship | varchar(100) | YES | | NULL | |
| streetcity | text | YES | | NULL | |
| phoneday | varchar(50) | YES | | NULL | |
| preferredcont | char(1) | YES | | NULL | |
| physstreet | varchar(100) | YES | | NULL | |
| homezipcode | varchar(25) | YES | | NULL | |
| zipcode | varchar(25) | YES | | NULL | |
| userid | varchar(30) | YES | | NULL | |
| password | varchar(30) | YES | | NULL | |
| flags | int(11) | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
* borrowernumber -- This value is automatically generated when a
patron is added. To add patrons in bulk, however, you may need to
generate this number manually. For instance, if you put your patron
information into a comma-delimited file and use MySQL's "LOAD DATA INFILE"
command to load your data (a very fast operation!), the first
comma-delimited field should contain a unique borrowernumber,
preferably sequential.
* cardnumber -- the user's library card "number." It does not
actually need to be a number.
* surname -- the user's family name
* firstname -- can actually be as many "first" names as necessary *
title -- e.g. "Mr."
* othernames -- names after the first name can be put here instead of
in firstname
* initials -- the person's initials, e.g. 'John Doe' = 'JD'; rarely
used and relatively unimportant
* streetaddress -- actually the mailing address; see also physstreet *
suburb -- useful in some countries
* city -- and also the state, etc., any other info needed for the
mailing address other than the postal code
* phone -- the telephone number
* emailaddress -- the e-mail address
* faxnumber -- the number of the person's fax machine
* textmessaging -- contact information for any text messaging device *
altstreetaddress -- street address of the person's alternate contact
(contactname)
* altsuburb -- alternate contact's suburb
* altcity -- alternate contact's city (and state/province AND postal
code)
* altphone -- alternate contact's telephone number
* dateofbirth -- should be entered in MySQL format (yyyy-mm-dd), Koha
will convert based on your parameters setting
* branchcode -- your code for the branch where the person enrolled *
categorycode -- your code for the type of member; 'C' is suggested
for children, since this triggers some of the guarantor options
* dateenrolled -- the date when the person enrolled, in MySQL format
(yyyy-mm-dd)
* gonenoaddress -- set to '1' if the person has moved without leaving
a forwarding address
* lost -- set to '1' if the person's library card has been reported
lost
* debarred -- set to '1' if the person has been "debarred" or blocked
from receiving library service for excessive overdues or fines, etc.
* studentnumber -- a student ID number, but could be used for any ID
number (e.g. Social Security number in the USA)
* school -- the student's school (for use with student number) *
contactname -- the name of the person's alternate contact
* borrowernotes -- any notes or messages about the person
* guarantor -- for children, this is the borrowernumber of the adult
parent or custodian
* area -- a two-letter code set by the library to identify the service
area in which the person resides (may be omitted)
* ethnicity -- useful for libraries that are required to track service
to ethnic minorities, but easily ignored
* ethnotes -- any notes related to ethnicity
* sex -- 'M' or 'F' (male or female); 'M' is the default
* expiry -- the date when the person's membership expires, in MySQL
format (yyyy-mm-dd)
* altnotes -- any notes or messages about the alternate contact *
altrelationship -- the nature of the alternate contact, can be
'workplace', 'relative','friend' or 'neighbour'
* streetcity -- goes with physstreet if the person lives in a city
other than their mailing address
* phoneday -- the daytime or "work" telephone number
* preferredcont -- a one-letter code indicating the person's preferred
contact method, e.g. 'E' for e-mail
* physstreet -- the actual street address, if the person has a
different mailing address (e.g. post office box)
* homezipcode -- the postal code of the person's physical street
address
* zipcode -- the postal code of the person's mailing address
* userid -- this is the username the person has chosen to use to gain
access to their account through the OPAC
* password -- this is the password the person has chosen to use to
gain access to their account through the OPAC; it cannot be plain
text, but must in encrypted using MD5 (md5_base64) before importing
* flags -- these are the person's privileges, which can be set when
editing their record. The privileges are stored as the decimal
representation of a binary number, with each binary digit indicating
whether the privilege is allowed ('1') or not allowed ('0').
Typically, you will want to give imported borrowers the privileges to
borrow books and reserve books for themselves, which can be set by
inserting the integer 384 in this column.
The most important pieces of information are the borrowernumber and the
cardnumber (required), the person's surname and firstname, their mailing
address, and the branch at which they enrolled. All of the other
information can be useful but may be omitted without affecting Koha's
operation significantly.
)endquote
Of course, another good way to get the information you need is just to ask
a specific question, such as "What is the timestamp column in the issues
table?" (Answer: don't worry about it, Koha fills this one in for you.)
--
Stephen Hedges
Skemotah Solutions, USA
www.skemotah.com -- shedges at skemotah.com
More information about the Koha
mailing list