Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | |||
|---|---|---|---|---|---|---|---|---|---|---|---|
| borrowernumber | INT | 10 | 0 |
|
|
primary key, Koha assigned ID number for patrons/borrowers |
|||||
| cardnumber | VARCHAR | 32 | √ | NULL |
|
|
unique key, library assigned ID number for patrons/borrowers |
||||
| surname | LONGTEXT | 2147483647 | √ | NULL |
|
|
patron/borrower’s last name (surname) |
||||
| firstname | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
patron/borrower’s first name |
||||
| preferred_name | LONGTEXT | 2147483647 | √ | NULL |
|
|
patron/borrower’s preferred name |
||||
| middle_name | LONGTEXT | 2147483647 | √ | NULL |
|
|
patron/borrower’s middle name |
||||
| title | LONGTEXT | 2147483647 | √ | NULL |
|
|
patron/borrower’s title, for example: Mr. or Mrs. |
||||
| othernames | LONGTEXT | 2147483647 | √ | NULL |
|
|
any other names associated with the patron/borrower |
||||
| initials | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
initials for your patron/borrower |
||||
| pronouns | LONGTEXT | 2147483647 | √ | NULL |
|
|
patron/borrower pronouns |
||||
| streetnumber | TINYTEXT | 255 | √ | NULL |
|
|
the house number for your patron/borrower’s primary address |
||||
| streettype | TINYTEXT | 255 | √ | NULL |
|
|
the street type (Rd., Blvd, etc) for your patron/borrower’s primary address |
||||
| address | LONGTEXT | 2147483647 | √ | NULL |
|
|
the first address line for your patron/borrower’s primary address |
||||
| address2 | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the second address line for your patron/borrower’s primary address |
||||
| city | LONGTEXT | 2147483647 | √ | NULL |
|
|
the city or town for your patron/borrower’s primary address |
||||
| state | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the state or province for your patron/borrower’s primary address |
||||
| zipcode | TINYTEXT | 255 | √ | NULL |
|
|
the zip or postal code for your patron/borrower’s primary address |
||||
| country | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the country for your patron/borrower’s primary address |
||||
| LONGTEXT | 2147483647 | √ | NULL |
|
|
the primary email address for your patron/borrower’s primary address |
|||||
| phone | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the primary phone number for your patron/borrower’s primary address |
||||
| mobile | TINYTEXT | 255 | √ | NULL |
|
|
the other phone number for your patron/borrower’s primary address |
||||
| fax | LONGTEXT | 2147483647 | √ | NULL |
|
|
the fax number for your patron/borrower’s primary address |
||||
| emailpro | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the secondary email address for your patron/borrower’s primary address |
||||
| phonepro | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the secondary phone number for your patron/borrower’s primary address |
||||
| B_streetnumber | TINYTEXT | 255 | √ | NULL |
|
|
the house number for your patron/borrower’s alternate address |
||||
| B_streettype | TINYTEXT | 255 | √ | NULL |
|
|
the street type (Rd., Blvd, etc) for your patron/borrower’s alternate address |
||||
| B_address | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the first address line for your patron/borrower’s alternate address |
||||
| B_address2 | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the second address line for your patron/borrower’s alternate address |
||||
| B_city | LONGTEXT | 2147483647 | √ | NULL |
|
|
the city or town for your patron/borrower’s alternate address |
||||
| B_state | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the state for your patron/borrower’s alternate address |
||||
| B_zipcode | TINYTEXT | 255 | √ | NULL |
|
|
the zip or postal code for your patron/borrower’s alternate address |
||||
| B_country | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the country for your patron/borrower’s alternate address |
||||
| B_email | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the patron/borrower’s alternate email address |
||||
| B_phone | LONGTEXT | 2147483647 | √ | NULL |
|
|
the patron/borrower’s alternate phone number |
||||
| dateofbirth | DATE | 10 | √ | NULL |
|
|
the patron/borrower’s date of birth (YYYY-MM-DD) |
||||
| branchcode | VARCHAR | 10 | '' |
|
|
foreign key from the branches table, includes the code of the patron/borrower’s home branch |
|||||
| categorycode | VARCHAR | 10 | '' |
|
|
foreign key from the categories table, includes the code of the patron category |
|||||
| dateenrolled | DATE | 10 | √ | NULL |
|
|
date the patron was added to Koha (YYYY-MM-DD) |
||||
| dateexpiry | DATE | 10 | √ | NULL |
|
|
date the patron/borrower’s card is set to expire (YYYY-MM-DD) |
||||
| password_expiration_date | DATE | 10 | √ | NULL |
|
|
date the patron/borrower’s password is set to expire (YYYY-MM-DD) |
||||
| date_renewed | DATE | 10 | √ | NULL |
|
|
date the patron/borrower’s card was last renewed |
||||
| gonenoaddress | TINYINT | 3 | √ | NULL |
|
|
set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address |
||||
| lost | TINYINT | 3 | √ | NULL |
|
|
set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card |
||||
| debarred | DATE | 10 | √ | NULL |
|
|
until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYYY-MM-DD) |
||||
| debarredcomment | VARCHAR | 255 | √ | NULL |
|
|
comment on the stop of patron |
||||
| contactname | LONGTEXT | 2147483647 | √ | NULL |
|
|
used for children and professionals to include surname or last name of guarantor or organization name |
||||
| contactfirstname | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
used for children to include first name of guarantor |
||||
| contacttitle | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
used for children to include title (Mr., Mrs., etc) of guarantor |
||||
| borrowernotes | LONGTEXT | 2147483647 | √ | NULL |
|
|
a note on the patron/borrower’s account that is only visible in the staff interface |
||||
| relationship | VARCHAR | 100 | √ | NULL |
|
|
used for children to include the relationship to their guarantor |
||||
| sex | VARCHAR | 1 | √ | NULL |
|
|
patron/borrower’s gender |
||||
| password | VARCHAR | 60 | √ | NULL |
|
|
patron/borrower’s encrypted password |
||||
| secret | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
Secret for 2FA |
||||
| auth_method | enum('password', 'two-factor') | 10 | 'password' |
|
|
Authentication method |
|||||
| flags | BIGINT | 19 | √ | NULL |
|
|
will include a number associated with the staff member’s permissions |
||||
| userid | VARCHAR | 75 | √ | NULL |
|
|
patron/borrower’s opac and/or staff interface log in |
||||
| opacnote | LONGTEXT | 2147483647 | √ | NULL |
|
|
a note on the patron/borrower’s account that is visible in the OPAC and staff interface |
||||
| contactnote | VARCHAR | 255 | √ | NULL |
|
|
a note related to the patron/borrower’s alternate address |
||||
| sort1 | VARCHAR | 80 | √ | NULL |
|
|
a field that can be used for any information unique to the library |
||||
| sort2 | VARCHAR | 80 | √ | NULL |
|
|
a field that can be used for any information unique to the library |
||||
| altcontactfirstname | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
first name of alternate contact for the patron/borrower |
||||
| altcontactsurname | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
surname or last name of the alternate contact for the patron/borrower |
||||
| altcontactaddress1 | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the first address line for the alternate contact for the patron/borrower |
||||
| altcontactaddress2 | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the second address line for the alternate contact for the patron/borrower |
||||
| altcontactaddress3 | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the city for the alternate contact for the patron/borrower |
||||
| altcontactstate | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the state for the alternate contact for the patron/borrower |
||||
| altcontactzipcode | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the zipcode for the alternate contact for the patron/borrower |
||||
| altcontactcountry | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the country for the alternate contact for the patron/borrower |
||||
| altcontactphone | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
the phone number for the alternate contact for the patron/borrower |
||||
| smsalertnumber | VARCHAR | 50 | √ | NULL |
|
|
the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on) |
||||
| sms_provider_id | INT | 10 | √ | NULL |
|
|
the provider of the mobile phone number defined in smsalertnumber |
||||
| privacy | INT | 10 | 1 |
|
|
patron/borrower’s privacy settings related to their checkout history KEY |
|||||
| privacy_guarantor_fines | TINYINT | 3 | 0 |
|
|
controls if relatives can see this patron’s fines |
|||||
| privacy_guarantor_checkouts | TINYINT | 3 | 0 |
|
|
controls if relatives can see this patron’s checkouts |
|||||
| checkprevcheckout | enum('yes', 'no', 'inherit') | 7 | 'inherit' |
|
|
produce a warning for this patron if this item has previously been checked out to this patron if ‘yes’, not if ‘no’, defer to category setting if ‘inherit’. |
|||||
| updated_on | TIMESTAMP | 19 | current_timestamp() |
|
|
time of last change could be useful for synchronization with external systems (among others) |
|||||
| lastseen | DATETIME | 19 | √ | NULL |
|
|
last time a patron has been seen (connected at the OPAC or staff interface) |
||||
| lang | VARCHAR | 25 | 'default' |
|
|
lang to use to send notices to this patron |
|||||
| login_attempts | INT | 10 | 0 |
|
|
number of failed login attempts |
|||||
| overdrive_auth_token | MEDIUMTEXT | 16777215 | √ | NULL |
|
|
persist OverDrive auth token |
||||
| anonymized | TINYINT | 3 | 0 |
|
|
flag for data anonymization |
|||||
| autorenew_checkouts | TINYINT | 3 | 1 |
|
|
flag for allowing auto-renewal |
|||||
| primary_contact_method | VARCHAR | 45 | √ | NULL |
|
|
useful for reporting purposes |
||||
| protected | TINYINT | 3 | 0 |
|
|
boolean flag to mark selected patrons as protected from deletion |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| borrowernumber | Performance | Asc | borrowernumber |
| cardnumber | Performance | Asc | cardnumber |
| sms_provider_id | Performance | Asc | sms_provider_id |
Relationships
Close relationships within degrees of separation
