Lines 205-210
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in
Link Here
|
205 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
205 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
206 |
|
206 |
|
207 |
-- |
207 |
-- |
|
|
208 |
-- Table structure for table `borrowers` |
209 |
-- |
210 |
|
211 |
DROP TABLE IF EXISTS `borrowers`; |
212 |
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members |
213 |
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers |
214 |
`cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers |
215 |
`surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) |
216 |
`firstname` text, -- patron/borrower's first name |
217 |
`title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. |
218 |
`othernames` mediumtext, -- any other names associated with the patron/borrower |
219 |
`initials` text, -- initials for your patron/borrower |
220 |
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address |
221 |
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address |
222 |
`address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address |
223 |
`address2` text, -- the second address line for your patron/borrower's primary address |
224 |
`city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address |
225 |
`state` text default NULL, -- the state or province for your patron/borrower's primary address |
226 |
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address |
227 |
`country` text, -- the country for your patron/borrower's primary address |
228 |
`email` mediumtext, -- the primary email address for your patron/borrower's primary address |
229 |
`phone` text, -- the primary phone number for your patron/borrower's primary address |
230 |
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address |
231 |
`fax` mediumtext, -- the fax number for your patron/borrower's primary address |
232 |
`emailpro` text, -- the secondary email addres for your patron/borrower's primary address |
233 |
`phonepro` text, -- the secondary phone number for your patron/borrower's primary address |
234 |
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address |
235 |
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address |
236 |
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address |
237 |
`B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address |
238 |
`B_city` mediumtext, -- the city or town for your patron/borrower's alternate address |
239 |
`B_state` text default NULL, -- the state for your patron/borrower's alternate address |
240 |
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address |
241 |
`B_country` text, -- the country for your patron/borrower's alternate address |
242 |
`B_email` text, -- the patron/borrower's alternate email address |
243 |
`B_phone` mediumtext, -- the patron/borrower's alternate phone number |
244 |
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) |
245 |
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch |
246 |
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category |
247 |
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) |
248 |
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) |
249 |
`gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address |
250 |
`lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card |
251 |
`debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) |
252 |
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron |
253 |
`contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name |
254 |
`contactfirstname` text, -- used for children to include first name of guarentor |
255 |
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor |
256 |
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations |
257 |
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client |
258 |
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor |
259 |
`sex` varchar(1) default NULL, -- patron/borrower's gender |
260 |
`password` varchar(60) default NULL, -- patron/borrower's encrypted password |
261 |
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions |
262 |
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in |
263 |
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client |
264 |
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address |
265 |
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
266 |
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
267 |
`altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower |
268 |
`altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower |
269 |
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower |
270 |
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower |
271 |
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower |
272 |
`altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower |
273 |
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower |
274 |
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower |
275 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
276 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
277 |
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber |
278 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
279 |
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts |
280 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
281 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
282 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
283 |
KEY `categorycode` (`categorycode`), |
284 |
KEY `branchcode` (`branchcode`), |
285 |
UNIQUE KEY `userid` (`userid`), |
286 |
KEY `guarantorid` (`guarantorid`), |
287 |
KEY `surname_idx` (`surname`(255)), |
288 |
KEY `firstname_idx` (`firstname`(255)), |
289 |
KEY `othernames_idx` (`othernames`(255)), |
290 |
KEY `sms_provider_id` (`sms_provider_id`), |
291 |
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), |
292 |
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), |
293 |
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL |
294 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
295 |
|
296 |
-- |
208 |
-- Table structure for table `borrower_attribute_types` |
297 |
-- Table structure for table `borrower_attribute_types` |
209 |
-- |
298 |
-- |
210 |
|
299 |
|
Lines 632-637
CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
Link Here
|
632 |
`checkprevcheckout` varchar(7) NOT NULL default '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'. |
721 |
`checkprevcheckout` varchar(7) NOT NULL default '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'. |
633 |
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others) |
722 |
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others) |
634 |
`lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface) |
723 |
`lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface) |
|
|
724 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
635 |
KEY borrowernumber (borrowernumber), |
725 |
KEY borrowernumber (borrowernumber), |
636 |
KEY `cardnumber` (`cardnumber`), |
726 |
KEY `cardnumber` (`cardnumber`), |
637 |
KEY `sms_provider_id` (`sms_provider_id`) |
727 |
KEY `sms_provider_id` (`sms_provider_id`) |
Lines 3402-3407
CREATE TABLE IF NOT EXISTS `borrower_modifications` (
Link Here
|
3402 |
`contactname` mediumtext, |
3492 |
`contactname` mediumtext, |
3403 |
`contactfirstname` text, |
3493 |
`contactfirstname` text, |
3404 |
`contacttitle` text, |
3494 |
`contacttitle` text, |
|
|
3495 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
3405 |
`guarantorid` int(11) DEFAULT NULL, |
3496 |
`guarantorid` int(11) DEFAULT NULL, |
3406 |
`borrowernotes` mediumtext, |
3497 |
`borrowernotes` mediumtext, |
3407 |
`relationship` varchar(100) DEFAULT NULL, |
3498 |
`relationship` varchar(100) DEFAULT NULL, |