The table suggestions is missing FK constraints and cascades to ensure a suggestion is anonymized when the borrower who suggested it gets deleted. The current table definition is: 2077 DROP TABLE IF EXISTS `suggestions`; 2078 CREATE TABLE `suggestions` ( -- purchase suggestions 2079 `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha 2080 `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table 2081 `suggesteddate` date NOT NULL default 0, -- date the suggestion was submitted 2082 `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table 2083 `manageddate` date default NULL, -- date the suggestion was updated 2084 acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table 2085 accepteddate date default NULL, -- date the suggestion was marked as accepted 2086 rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table 2087 rejecteddate date default NULL, -- date the suggestion was marked as rejected 2088 `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) 2089 `note` mediumtext, -- note entered on the suggestion 2090 `author` varchar(80) default NULL, -- author of the suggested item 2091 `title` varchar(255) default NULL, -- title of the suggested item 2092 `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item 2093 `publishercode` varchar(255) default NULL, -- publisher of the suggested item 2094 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated 2095 `volumedesc` varchar(255) default NULL, 2096 `publicationyear` smallint(6) default 0, 2097 `place` varchar(255) default NULL, -- publication place of the suggested item 2098 `isbn` varchar(30) default NULL, -- isbn of the suggested item 2099 `mailoverseeing` smallint(1) default 0, 2100 `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered 2101 `reason` text, -- reason for accepting or rejecting the suggestion 2102 `patronreason` text, -- reason for making the suggestion 2103 budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table 2104 branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table 2105 collectiontitle text default NULL, -- collection name for the suggested item 2106 itemtype VARCHAR(30) default NULL, -- suggested item type 2107 quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased 2108 currency VARCHAR(3) default NULL, -- suggested currency for the suggested price 2109 price DECIMAL(28,6) default NULL, -- suggested price 2110 total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) 2111 PRIMARY KEY (`suggestionid`), 2112 KEY `suggestedby` (`suggestedby`), 2113 KEY `managedby` (`managedby`) 2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
At first glance, I think the desired action for the FK constraints is "on delete set null, on update cascade". That raises a question for the suggestedby column: should it be made nullable? My gut feeling is yes -- I could see wanting to be able to report on suggestions over time, even in cases where the patron who originally made the suggestion has gone away.
*** This bug has been marked as a duplicate of bug 13533 ***