MergingOfTables
Glourious plan to merge tables
Why?
In Koha's DB, instead of having a "soft" deletion of entries by flagging the deleted one as so, the rows in the db is actually deleted, but only after a new row in a different table is inserted.
Koha's DB is relational, so there is a cascade of links between each object: a biblio is referred by many items, and each item is referred by issues, holds, messages.
E.g.: when a row in the item table is moved from "items" to "deleted_items", then all the references from holds, transfers and so on will be broken.
The suggested more orthodox approach to this would be to add a "deleted_at" column, which is normally null unless the row is deleted, then it set to the timestamp of when it was deletion.
This means that the row is still there and will still be referred by other tables, but it just won't be shown in the relevant lists.
This would improve the maintenance of Koha in many ways (e.g.: the auto_increment id on those tables caused lots of issues when you add an item and delete it immediately after. the next new item will have the same itemnumber as the previously deleted one, clashing with it in many ways, see https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix ).
Previous discussions
Relevant discussions on mailing lists:
- 2017 July: AUTO_INCREMENT fix - prevent data lost
- 2016 January: Merge borrowers and deletedborrowers tables
Which tables?
- deletedborrowers => borrowers (CONSIDER: maybe postpone this?)
- deletedbiblio => biblio
- deletedbiblioitems => biblioitems
- deletedbiblio_metadata => biblio_metadata
- deleteditems => items
- old_issues => issues
- old_reserves => reserves
How
Add a deleted column to the tables, and merge old_/deleted-tables into them. Column could be a booelan (tinyint in MySQL) or maybe better deleted_at with a timestamp denoting when the borrower/item/reserve etc was deleted/obsoleted.
Bugzilla bugs
a initial patch for items, biblio (and corollary) pushed to bz
Other tasks
- Update SQL reports library
Bugs and Issues
- barcode in items table is UNIQUE, so deleting an item won't let you reuse its barcode again after.
- maybe some view in koha should shows all the items (with the deleted ones grayed out) and allow un-delete (this should be a follow up task IMHO)
- deletedborrowers table isn't used anywhere. It's fairly easy to merge them but any unique indexes (email?) would cause several problems. I think this need more planning.