On Thu Jan 19 03:03:51 2017, MICHIELB wrote:
Show quoted text>
> Can you please share the CREATE TABLE statement of your 'borrowers' table?
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
`cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers
`surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
`firstname` text, -- patron/borrower's first name
`title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
`othernames` mediumtext, -- any other names associated with the patron/borrower
`initials` text, -- initials for your patron/borrower
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
`address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
`address2` text, -- the second address line for your patron/borrower's primary address
`city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
`state` text default NULL, -- the state or province for your patron/borrower's primary address
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
`country` text, -- the country for your patron/borrower's primary address
`email` mediumtext, -- the primary email address for your patron/borrower's primary address
`phone` text, -- the primary phone number for your patron/borrower's primary address
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
`fax` mediumtext, -- the fax number for your patron/borrower's primary address
`emailpro` text, -- the secondary email addres for your patron/borrower's primary address
`phonepro` text, -- the secondary phone number for your patron/borrower's primary address
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
`B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
`B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
`B_state` text default NULL, -- the state for your patron/borrower's alternate address
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
`B_country` text, -- the country for your patron/borrower's alternate address
`B_email` text, -- the patron/borrower's alternate email address
`B_phone` mediumtext, -- the patron/borrower's alternate phone number
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
`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
`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
`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)
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron
`contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
`contactfirstname` text, -- used for children to include first name of guarentor
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
`sex` varchar(1) default NULL, -- patron/borrower's gender
`password` varchar(60) default NULL, -- patron/borrower's encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
`altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
`altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
`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'.
`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)
`lastseen` datetime default NULL, -- last time a patron has been seed (connected at the OPAC or staff interface)
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
KEY `branchcode` (`branchcode`),
UNIQUE KEY `userid` (`userid`),
KEY `guarantorid` (`guarantorid`),
KEY `surname_idx` (`surname`(255)),
KEY `firstname_idx` (`firstname`(255)),
KEY `othernames_idx` (`othernames`(255)),
KEY `sms_provider_id` (`sms_provider_id`),
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
