--
DROP TABLE IF EXISTS `branchcategories`;
-CREATE TABLE `branchcategories` ( -- information related to library/branch groups
- `categorycode` varchar(10) NOT NULL default '', -- unique key, used to identify the group
- `categoryname` varchar(32), -- name used to identify the group
- `codedescription` mediumtext, -- description of the group
- `categorytype` varchar(16), -- defines if this is a search or properties group
+CREATE TABLE `branchcategories` ( -- information related to library/branch groups
+ `categorycode` varchar(10) NOT NULL default '', -- unique identifier for the library/branch group
+ `categoryname` varchar(32), -- name of the library/branch group
+ `codedescription` mediumtext, -- longer description of the library/branch group
+ `categorytype` varchar(16), -- says whether this is a search group or a properties group
PRIMARY KEY (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `branchrelations`;
-CREATE TABLE `branchrelations` ( -- tracks which libraries/branches are in each library/branch group
- `branchcode` varchar(10) NOT NULL default '', -- foreign key linking to the branches table
- `categorycode` varchar(10) NOT NULL default '', -- foreign key linking to the branchcategories table
+CREATE TABLE `branchrelations` ( -- this table links libraries/branches to groups
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to identify the branch
+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the branchcategories table to identify the group
PRIMARY KEY (`branchcode`,`categorycode`),
KEY `branchcode` (`branchcode`),
KEY `categorycode` (`categorycode`),
--
DROP TABLE IF EXISTS `itemtypes`;
-CREATE TABLE `itemtypes` (
- `itemtype` varchar(10) NOT NULL default '',
- `description` mediumtext,
- `rentalcharge` double(16,4) default NULL,
- `notforloan` smallint(6) default NULL,
- `imageurl` varchar(200) default NULL,
- `summary` text,
+CREATE TABLE `itemtypes` ( -- defines the item types
+ `itemtype` varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
+ `description` mediumtext, -- a plain text explanation of the item type
+ `rentalcharge` double(16,4) default NULL, -- the amount charged when this item is checked out/issued
+ `notforloan` smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
+ `imageurl` varchar(200) default NULL, -- URL for the item type icon
+ `summary` text, -- information from the summary field, may include HTML
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `letter`;
-CREATE TABLE `letter` (
- `module` varchar(20) NOT NULL default '',
- `code` varchar(20) NOT NULL default '',
- `name` varchar(100) NOT NULL default '',
- `title` varchar(200) NOT NULL default '',
- `content` text,
+CREATE TABLE `letter` ( -- table for all notice templates in Koha
+ `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice
+ `code` varchar(20) NOT NULL default '', -- unique identifier for this notice
+ `name` varchar(100) NOT NULL default '', -- plain text name for this notice
+ `title` varchar(200) NOT NULL default '', -- subject line of the notice
+ `content` text, -- body text for the notice
PRIMARY KEY (`module`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `opac_news`;
-CREATE TABLE `opac_news` (
- `idnew` int(10) unsigned NOT NULL auto_increment,
- `title` varchar(250) NOT NULL default '',
- `new` text NOT NULL,
- `lang` varchar(25) NOT NULL default '',
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
- `expirationdate` date default NULL,
- `number` int(11) default NULL,
+CREATE TABLE `opac_news` ( -- data from the news tool
+ `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
+ `title` varchar(250) NOT NULL default '', -- title of the news article
+ `new` text NOT NULL, -- the body of your news article
+ `lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac)
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time
+ `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible
+ `number` int(11) default NULL, -- the order in which this article appears in that specific location
PRIMARY KEY (`idnew`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `overduerules`;
-CREATE TABLE `overduerules` (
- `branchcode` varchar(10) NOT NULL default '',
- `categorycode` varchar(10) NOT NULL default '',
- `delay1` int(4) default NULL,
- `letter1` varchar(20) default NULL,
- `debarred1` varchar(1) default 0,
- `delay2` int(4) default NULL,
- `debarred2` varchar(1) default 0,
- `letter2` varchar(20) default NULL,
- `delay3` int(4) default NULL,
- `letter3` varchar(20) default NULL,
- `debarred3` int(1) default 0,
+CREATE TABLE `overduerules` ( -- overdue notice status and triggers
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
+ `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
+ `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
+ `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
+ `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
+ `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
+ `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
+ `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
+ `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
+ `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
PRIMARY KEY (`branchcode`,`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `repeatable_holidays`;
-CREATE TABLE `repeatable_holidays` (
- `id` int(11) NOT NULL auto_increment,
- `branchcode` varchar(10) NOT NULL default '',
- `weekday` smallint(6) default NULL,
- `day` smallint(6) default NULL,
- `month` smallint(6) default NULL,
- `title` varchar(50) NOT NULL default '',
- `description` text NOT NULL,
+CREATE TABLE `repeatable_holidays` ( -- information for the days the library is closed
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
+ `weekday` smallint(6) default NULL, -- day of the week (0=Sunday, 1=Monday, etc) this closing is repeated on
+ `day` smallint(6) default NULL, -- day of the month this closing is on
+ `month` smallint(6) default NULL, -- month this closing is in
+ `title` varchar(50) NOT NULL default '', -- title of this closing
+ `description` text NOT NULL, -- description for this closing
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `reports_dictionary`;
-CREATE TABLE reports_dictionary (
- `id` int(11) NOT NULL auto_increment,
- `name` varchar(255) default NULL,
- `description` text,
- `date_created` datetime default NULL,
- `date_modified` datetime default NULL,
- `saved_sql` text,
- `area` int(11) default NULL,
+CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for use in reports
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `name` varchar(255) default NULL, -- name for this definition
+ `description` text, -- description for this definition
+ `date_created` datetime default NULL, -- date and time this definition was created
+ `date_modified` datetime default NULL, -- date and time this definition was last modified
+ `saved_sql` text, -- SQL snippet for us in reports
+ `area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts)
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `reviews`;
-CREATE TABLE `reviews` (
- `reviewid` int(11) NOT NULL auto_increment,
- `borrowernumber` int(11) default NULL,
- `biblionumber` int(11) default NULL,
- `review` text,
- `approved` tinyint(4) default NULL,
- `datereviewed` datetime default NULL,
+CREATE TABLE `reviews` ( -- patron opac comments
+ `reviewid` int(11) NOT NULL auto_increment, -- unique identifier for this comment
+ `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron left this comment
+ `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bibliographic record this comment is for
+ `review` text, -- the body of the comment
+ `approved` tinyint(4) default NULL, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
+ `datereviewed` datetime default NULL, -- the date the comment was left
PRIMARY KEY (`reviewid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `roadtype`;
-CREATE TABLE `roadtype` (
- `roadtypeid` int(11) NOT NULL auto_increment,
- `road_type` varchar(100) NOT NULL default '',
+CREATE TABLE `roadtype` ( -- road types defined in administration and used in patron management
+ `roadtypeid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha for each road type
+ `road_type` varchar(100) NOT NULL default '', -- text for road type
PRIMARY KEY (`roadtypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `special_holidays`;
-CREATE TABLE `special_holidays` (
- `id` int(11) NOT NULL auto_increment,
- `branchcode` varchar(10) NOT NULL default '',
- `day` smallint(6) NOT NULL default 0,
- `month` smallint(6) NOT NULL default 0,
- `year` smallint(6) NOT NULL default 0,
- `isexception` smallint(1) NOT NULL default 1,
- `title` varchar(50) NOT NULL default '',
- `description` text NOT NULL,
+CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
+ `day` smallint(6) NOT NULL default 0, -- day of the month this closing is on
+ `month` smallint(6) NOT NULL default 0, -- month this closing is in
+ `year` smallint(6) NOT NULL default 0, -- year this closing is in
+ `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
+ `title` varchar(50) NOT NULL default '', -- title for this closing
+ `description` text NOT NULL, -- description of this closing
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `subscriptionroutinglist`;
-CREATE TABLE `subscriptionroutinglist` (
- `routingid` int(11) NOT NULL auto_increment,
- `borrowernumber` int(11) NOT NULL,
- `ranking` int(11) default NULL,
- `subscriptionid` int(11) NOT NULL,
+CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing lists attached to subscriptions
+ `routingid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines with patron is on the routing list
+ `ranking` int(11) default NULL, -- where the patron stands in line to receive the serial
+ `subscriptionid` int(11) NOT NULL, -- foreign key from the subscription table, defines which subscription this routing list is for
PRIMARY KEY (`routingid`),
UNIQUE (`subscriptionid`, `borrowernumber`),
CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
--
DROP TABLE IF EXISTS `suggestions`;
-CREATE TABLE `suggestions` (
- `suggestionid` int(8) NOT NULL auto_increment,
- `suggestedby` int(11) NOT NULL default 0,
- `suggesteddate` date NOT NULL default 0,
- `managedby` int(11) default NULL,
- `manageddate` date default NULL,
- acceptedby INT(11) default NULL,
- accepteddate date default NULL,
- rejectedby INT(11) default NULL,
- rejecteddate date default NULL,
- `STATUS` varchar(10) NOT NULL default '',
- `note` mediumtext,
- `author` varchar(80) default NULL,
- `title` varchar(80) default NULL,
- `copyrightdate` smallint(6) default NULL,
- `publishercode` varchar(255) default NULL,
- `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+CREATE TABLE `suggestions` ( -- purchase suggestions
+ `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha
+ `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
+ `suggesteddate` date NOT NULL default 0, -- date the suggestion was submitted
+ `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table
+ `manageddate` date default NULL, -- date the suggestion was updated
+ acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table
+ accepteddate date default NULL, -- date the suggestion was marked as accepted
+ rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table
+ rejecteddate date default NULL, -- date the suggestion was marked as rejected
+ `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED)
+ `note` mediumtext, -- note entered on the suggestion
+ `author` varchar(80) default NULL, -- author of the suggested item
+ `title` varchar(80) default NULL, -- title of the suggested item
+ `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item
+ `publishercode` varchar(255) default NULL, -- publisher of the suggested item
+ `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated
`volumedesc` varchar(255) default NULL,
`publicationyear` smallint(6) default 0,
- `place` varchar(255) default NULL,
- `isbn` varchar(30) default NULL,
+ `place` varchar(255) default NULL, -- publication place of the suggested item
+ `isbn` varchar(30) default NULL, -- isbn of the suggested item
`mailoverseeing` smallint(1) default 0,
- `biblionumber` int(11) default NULL,
- `reason` text,
- budgetid INT(11),
- branchcode VARCHAR(10) default NULL,
- collectiontitle text default NULL,
- itemtype VARCHAR(30) default NULL,
- quantity SMALLINT(6) default NULL,
- currency VARCHAR(3) default NULL,
- price DECIMAL(28,6) default NULL,
- total DECIMAL(28,6) default NULL,
+ `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
+ `reason` text, -- reason for making the suggestion
+ budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table
+ branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
+ collectiontitle text default NULL, -- collection name for the suggested item
+ itemtype VARCHAR(30) default NULL, -- suggested item type
+ quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
+ currency VARCHAR(3) default NULL, -- suggested currency for the suggested price
+ price DECIMAL(28,6) default NULL, -- suggested price
+ total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency)
PRIMARY KEY (`suggestionid`),
KEY `suggestedby` (`suggestedby`),
KEY `managedby` (`managedby`)
--
DROP TABLE IF EXISTS `systempreferences`;
-CREATE TABLE `systempreferences` (
- `variable` varchar(50) NOT NULL default '',
- `value` text,
- `options` mediumtext,
- `explanation` text,
- `type` varchar(20) default NULL,
+CREATE TABLE `systempreferences` ( -- global system preferences
+ `variable` varchar(50) NOT NULL default '', -- system preference name
+ `value` text, -- system preference values
+ `options` mediumtext, -- options for multiple choice system preferences
+ `explanation` text, -- descriptive text for the system preference
+ `type` varchar(20) default NULL, -- type of question this preference asks (multiple choice, plain text, yes or no, etc)
PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `virtualshelves`;
-CREATE TABLE `virtualshelves` (
- `shelfnumber` int(11) NOT NULL auto_increment,
- `shelfname` varchar(255) default NULL,
- `owner` varchar(80) default NULL,
- `category` varchar(1) default NULL,
- `sortfield` varchar(16) default NULL,
- `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves)
+ `shelfnumber` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `shelfname` varchar(255) default NULL, -- name of the list
+ `owner` varchar(80) default NULL, -- foriegn key linking to the borrowers table (using borrowernumber) for the creator of this list
+ `category` varchar(1) default NULL, -- type of list (public [2], private [1] or open [3])
+ `sortfield` varchar(16) default NULL, -- the field this list is sorted on
+ `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified
PRIMARY KEY (`shelfnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `virtualshelfcontents`;
-CREATE TABLE `virtualshelfcontents` (
- `shelfnumber` int(11) NOT NULL default 0,
- `biblionumber` int(11) NOT NULL default 0,
+CREATE TABLE `virtualshelfcontents` ( -- information about the titles in a list (or virtual shelf)
+ `shelfnumber` int(11) NOT NULL default 0, -- foreign key linking to the virtualshelves table, defines the list that this record has been added to
+ `biblionumber` int(11) NOT NULL default 0, -- foreign key linking to the biblio table, defines the bib record that has been added to the list
`flags` int(11) default NULL,
- `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time this bib record was added to the list
KEY `shelfnumber` (`shelfnumber`),
KEY `biblionumber` (`biblionumber`),
CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
--
DROP TABLE IF EXISTS `z3950servers`;
-CREATE TABLE `z3950servers` (
- `host` varchar(255) default NULL,
- `port` int(11) default NULL,
- `db` varchar(255) default NULL,
- `userid` varchar(255) default NULL,
- `password` varchar(255) default NULL,
- `name` mediumtext,
- `id` int(11) NOT NULL auto_increment,
- `checked` smallint(6) default NULL,
- `rank` int(11) default NULL,
- `syntax` varchar(80) default NULL,
- `icon` text,
+CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets used in cataloging
+ `host` varchar(255) default NULL, -- target's host name
+ `port` int(11) default NULL, -- port number used to connect to target
+ `db` varchar(255) default NULL, -- target's database name
+ `userid` varchar(255) default NULL, -- username needed to log in to target
+ `password` varchar(255) default NULL, -- password needed to log in to target
+ `name` mediumtext, -- name given to the target by the library
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `checked` smallint(6) default NULL, -- whether this target is checked by default (1 for yes, 0 for no)
+ `rank` int(11) default NULL, -- where this target appears in the list of targets
+ `syntax` varchar(80) default NULL, -- marc format provided by this target
+ `icon` text, -- unused in Koha
`position` enum('primary','secondary','') NOT NULL default 'primary',
`type` enum('zed','opensearch') NOT NULL default 'zed',
- `encoding` text default NULL,
- `description` text NOT NULL,
+ `encoding` text default NULL, -- characters encoding provided by this target
+ `description` text NOT NULL, -- unused in Koha
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table structure for table `messages`
--
DROP TABLE IF EXISTS `messages`;
-CREATE TABLE `messages` (
- `message_id` int(11) NOT NULL auto_increment,
- `borrowernumber` int(11) NOT NULL,
- `branchcode` varchar(10) default NULL,
- `message_type` varchar(1) NOT NULL,
- `message` text NOT NULL,
- `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+CREATE TABLE `messages` ( -- circulation messages left via the patron's check out screen
+ `message_id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `borrowernumber` int(11) NOT NULL, -- foreign key linking this message to the borrowers table
+ `branchcode` varchar(10) default NULL, -- foreign key linking the message to the branches table
+ `message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B)
+ `message` text NOT NULL, -- the text of the message
+ `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `fieldmapping`;
-CREATE TABLE `fieldmapping` (
- `id` int(11) NOT NULL auto_increment,
- `field` varchar(255) NOT NULL,
- `frameworkcode` char(4) NOT NULL default '',
- `fieldcode` char(3) NOT NULL,
- `subfieldcode` char(1) NOT NULL,
+CREATE TABLE `fieldmapping` ( -- koha to keyword mapping
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `field` varchar(255) NOT NULL, -- keyword to be mapped to (ex. subtitle)
+ `frameworkcode` char(4) NOT NULL default '', -- foreign key from the biblio_framework table to link this mapping to a specific framework
+ `fieldcode` char(3) NOT NULL, -- marc field number to map to this keyword
+ `subfieldcode` char(1) NOT NULL, -- marc subfield associated with the fieldcode to map to this keyword
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
projects / koha.git / blobdiff