Lines 194-199
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in
Link Here
|
194 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
194 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
195 |
|
195 |
|
196 |
-- |
196 |
-- |
|
|
197 |
-- Table structure for table `borrowers` |
198 |
-- |
199 |
|
200 |
DROP TABLE IF EXISTS `borrowers`; |
201 |
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members |
202 |
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers |
203 |
`cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers |
204 |
`surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) |
205 |
`firstname` text, -- patron/borrower's first name |
206 |
`title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. |
207 |
`othernames` mediumtext, -- any other names associated with the patron/borrower |
208 |
`initials` text, -- initials for your patron/borrower |
209 |
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address |
210 |
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address |
211 |
`address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address |
212 |
`address2` text, -- the second address line for your patron/borrower's primary address |
213 |
`city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address |
214 |
`state` text default NULL, -- the state or province for your patron/borrower's primary address |
215 |
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address |
216 |
`country` text, -- the country for your patron/borrower's primary address |
217 |
`email` mediumtext, -- the primary email address for your patron/borrower's primary address |
218 |
`phone` text, -- the primary phone number for your patron/borrower's primary address |
219 |
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address |
220 |
`fax` mediumtext, -- the fax number for your patron/borrower's primary address |
221 |
`emailpro` text, -- the secondary email addres for your patron/borrower's primary address |
222 |
`phonepro` text, -- the secondary phone number for your patron/borrower's primary address |
223 |
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address |
224 |
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address |
225 |
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address |
226 |
`B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address |
227 |
`B_city` mediumtext, -- the city or town for your patron/borrower's alternate address |
228 |
`B_state` text default NULL, -- the state for your patron/borrower's alternate address |
229 |
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address |
230 |
`B_country` text, -- the country for your patron/borrower's alternate address |
231 |
`B_email` text, -- the patron/borrower's alternate email address |
232 |
`B_phone` mediumtext, -- the patron/borrower's alternate phone number |
233 |
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) |
234 |
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch |
235 |
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category |
236 |
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) |
237 |
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) |
238 |
`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 |
239 |
`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 |
240 |
`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) |
241 |
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron |
242 |
`contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name |
243 |
`contactfirstname` text, -- used for children to include first name of guarentor |
244 |
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor |
245 |
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations |
246 |
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client |
247 |
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor |
248 |
`sex` varchar(1) default NULL, -- patron/borrower's gender |
249 |
`password` varchar(60) default NULL, -- patron/borrower's encrypted password |
250 |
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions |
251 |
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in |
252 |
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client |
253 |
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address |
254 |
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
255 |
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
256 |
`altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower |
257 |
`altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower |
258 |
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower |
259 |
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower |
260 |
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower |
261 |
`altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower |
262 |
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower |
263 |
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower |
264 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
265 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
266 |
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber |
267 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
268 |
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts |
269 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
270 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
271 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
272 |
KEY `categorycode` (`categorycode`), |
273 |
KEY `branchcode` (`branchcode`), |
274 |
UNIQUE KEY `userid` (`userid`), |
275 |
KEY `guarantorid` (`guarantorid`), |
276 |
KEY `surname_idx` (`surname`(255)), |
277 |
KEY `firstname_idx` (`firstname`(255)), |
278 |
KEY `othernames_idx` (`othernames`(255)), |
279 |
KEY `sms_provider_id` (`sms_provider_id`), |
280 |
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), |
281 |
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), |
282 |
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL |
283 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
284 |
|
285 |
-- |
197 |
-- Table structure for table `borrower_attribute_types` |
286 |
-- Table structure for table `borrower_attribute_types` |
198 |
-- |
287 |
-- |
199 |
|
288 |
|
Lines 622-627
CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
Link Here
|
622 |
`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'. |
711 |
`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'. |
623 |
`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) |
712 |
`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) |
624 |
`lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface) |
713 |
`lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface) |
|
|
714 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
625 |
KEY borrowernumber (borrowernumber), |
715 |
KEY borrowernumber (borrowernumber), |
626 |
KEY `cardnumber` (`cardnumber`), |
716 |
KEY `cardnumber` (`cardnumber`), |
627 |
KEY `sms_provider_id` (`sms_provider_id`) |
717 |
KEY `sms_provider_id` (`sms_provider_id`) |
Lines 3397-3402
CREATE TABLE IF NOT EXISTS `borrower_modifications` (
Link Here
|
3397 |
`altcontactphone` varchar(50) DEFAULT NULL, |
3487 |
`altcontactphone` varchar(50) DEFAULT NULL, |
3398 |
`smsalertnumber` varchar(50) DEFAULT NULL, |
3488 |
`smsalertnumber` varchar(50) DEFAULT NULL, |
3399 |
`privacy` int(11) DEFAULT NULL, |
3489 |
`privacy` int(11) DEFAULT NULL, |
|
|
3490 |
`primary_contact_method` varchar(45) DEFAULT NULL, -- useful for reporting purposes |
3400 |
PRIMARY KEY (`verification_token`,`borrowernumber`), |
3491 |
PRIMARY KEY (`verification_token`,`borrowernumber`), |
3401 |
KEY `verification_token` (`verification_token`), |
3492 |
KEY `verification_token` (`verification_token`), |
3402 |
KEY `borrowernumber` (`borrowernumber`) |
3493 |
KEY `borrowernumber` (`borrowernumber`) |