Bug 10633 - Table suggestions needs some work
Summary: Table suggestions needs some work
Status: RESOLVED DUPLICATE of bug 13533
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-07-23 16:23 UTC by Katrin Fischer
Modified: 2015-01-08 14:42 UTC (History)
0 users

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2013-07-23 16:23:39 UTC
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;
Comment 1 Galen Charlton 2013-07-23 23:17:09 UTC
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.
Comment 2 Katrin Fischer 2015-01-08 14:42:27 UTC

*** This bug has been marked as a duplicate of bug 13533 ***