Bug 7021 - patron category in the statistics table
Summary: patron category in the statistics table
Status: ASSIGNED
Alias: None
Product: Koha
Classification: Unclassified
Component: Circulation (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement with 5 votes (vote)
Assignee: mathieu saby
QA Contact: Bugs List
URL:
Keywords:
: 10778 (view as bug list)
Depends on: 22794
Blocks:
  Show dependency treegraph
 
Reported: 2011-10-13 11:59 UTC by Nicole C. Engard
Modified: 2019-04-28 22:45 UTC (History)
8 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug_7021-Add_borrowers_category_to_statistics_usercode (6.65 KB, patch)
2014-07-02 17:09 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review
Bug 7021 - patron category in the statistics table (6.70 KB, patch)
2014-07-04 07:39 UTC, Olli-Antti Kivilahti
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nicole C. Engard 2011-10-13 11:59:01 UTC
The patron category code should be in the statistics table.  We track the item type and item number, but we only track the borrowernumber for the patron and part way through the year the patron's category could change from child to adult - or the patron could be deleted and that will mess with the statistics on circulation/actions based on patron category.

Once that data is added to that table, the circulation stats wizard will need to be updated as well.
Comment 1 Katrin Fischer 2011-10-13 12:00:25 UTC
I like that idea - not only for when patrons are deleted, but also for anonymization. Having the patron category will be great for statistics in those cases.
Comment 2 Liz Rea 2011-11-11 00:58:24 UTC
oh yes, instead of just nuking the borrower out of statistics, we can only null their borrowernumber and leave their category. That would definitely make our librarians a bit happier about anonymization.
Comment 3 Nicole C. Engard 2013-04-15 12:28:42 UTC
I got asked for this again today, so I'm bumping it :)
Comment 4 mathieu saby 2013-08-24 11:29:01 UTC
Hello
I have just realized I've created a bug for the same idea, but with other informations to store. Could you take a look? (Bug 10778). If you agree I can write a patch.

M. Saby
Comment 5 mathieu saby 2013-09-01 21:52:26 UTC
I copy the content of the bug Bug 10778 and close it, as it is a duplicate
Mathieu

Curently, statistic table contains some information about the item and the borrower (http://schema.koha-community.org/tables/statistics.html):
For the item : 
- itemnumber
- itemtype
- ccode
For the borrower
- borrowernumber
- associatedborrower
- and maybe "usercode" (not documented in schemaspy, so I don't know what it means).

I think we should store more information for the borrower : if the borrower record is deleted, we will not be able to get the useful informations (categorycode...) with a JOIN.

I am not sure of Koha's behavior if the item or biblio is deleted : if the itemnumber and biblionumber is deleted from statistic table, we could also store more information (as homebranch, location, biblio.serial, biblioitems.itemtype)

For the borrowers, I think we need :
- categorycode
- branchcode
- sex
- sort1
- sort2
and optionally the ability to record 3 or 4 custom fields with a syspref (for example, to store the extended attributes).

Do you agree with that ?

M. Saby
Comment 6 mathieu saby 2013-09-01 21:52:45 UTC
*** Bug 10778 has been marked as a duplicate of this bug. ***
Comment 7 Katrin Fischer 2013-09-02 07:15:59 UTC
Some of the information proposed by Mathieu is or might be a bit critical - so it would be nice to have a system preference to determine which fields are going to be saved into the statistics table. Maybe we could add the patron category as a first step.
Comment 8 mathieu saby 2013-09-08 11:19:14 UTC
(In reply to Katrin Fischer from comment #7)
> Some of the information proposed by Mathieu is or might be a bit critical -
> so it would be nice to have a system preference to determine which fields
> are going to be saved into the statistics table.

I agreee

 Maybe we could add the
> patron category as a first step.

OK for the categorycode in a 1st step. I think the library of the patron could be recorded too. It is not critical.

Do you think we need to store more information about items & biblios too ?

Mathieu
Comment 9 Katrin Fischer 2013-09-08 11:27:11 UTC
I think items and biblio information is stord in the deleted* tables and can be looked up from there - data privacy not a problem here, so the data doesn't need to be deleted regularly.
Comment 10 mathieu saby 2013-09-08 13:12:31 UTC
The bug was assigned to Kyle, but he is not in the contacts. I add him.
Kyle, do you still want to work on this?

Mathieu
Comment 11 mathieu saby 2013-11-09 15:48:21 UTC
I misread bug's status. It was not assigned. I take it and will propose a patch.
For me some info about items and biblio MUST be recorded in this table. Because item location (like patron category) can change over time.
The case occured in my library network (books moved from one library to one other), and it was complicated to get the original location.

Mathieu
Comment 12 mathieu saby 2013-11-09 15:54:21 UTC
I will add
for the item : 
- holdingbranch
- homebranch
- callnumber
- location
- dateaccessioned

for the borrower
- categorycode
- branchcode
- sex
- sort1
- sort2

for the record
- biblioitems.itemtype (as document_type)
- biblio.serial

Other interesting fields could be
items.barcode
items.price
items.replacementprice
items.issues
items.renewal
biblio.title
biblio.author
biblio.publicationyear
biblio.publishercode
and extended attributes of patrons


Mathieu
Comment 13 mathieu saby 2014-04-03 16:35:47 UTC
Before working on that, I would like bug 11230 to be tested.

Mathieu
Comment 14 Olli-Antti Kivilahti 2014-07-02 17:08:08 UTC
lol
Comment 15 Olli-Antti Kivilahti 2014-07-02 17:09:43 UTC Comment hidden (obsolete)
Comment 16 Olli-Antti Kivilahti 2014-07-02 17:13:12 UTC
BTW.
I am not very keen on adding lots of content to statistics-table.
Because it already is gettting verrrrry large.
I am not looking forward to it getting larger than absolutely necessary.

We already have old_issues and deleted* to keep as history.

Our SSD for the DB is only 100GB ;)


However I have no data to back up anything. It is possible that suggested modifications won't enlargen the statistics table significantly.
Comment 17 mathieu saby 2014-07-03 11:24:14 UTC
(In reply to Olli-Antti Kivilahti from comment #16)
> BTW.
> I am not very keen on adding lots of content to statistics-table.
> Because it already is gettting verrrrry large.
> I am not looking forward to it getting larger than absolutely necessary.
> 
> We already have old_issues and deleted* to keep as history.
> 
> Our SSD for the DB is only 100GB ;)
> 
> 
> However I have no data to back up anything. It is possible that suggested
> modifications won't enlargen the statistics table significantly.

The way Koha is storing statistics is not sufficient at all for a lot of libraries : if you delete some borrower's account, if a borrower's category changes (common in academic libraries), or if you anonymize transactions after a few months (mandatory in some countries), the information stored in statistics table is not sufficient to calculate some figures needed in our annual reports.

However, I'm concerned with the "DB size" issue... Do you know if creating columns in a table but NOT filling them (it could depend on the activation of a syspref) will have a significant effect on the size of the database?

Mathieu
Comment 18 mathieu saby 2014-07-03 11:25:27 UTC
By the way, bug 11230 has been signed off.
Comment 19 Olli-Antti Kivilahti 2014-07-04 07:39:15 UTC
Created attachment 29508 [details] [review]
Bug 7021 -  patron category in the statistics table

This patch populates the koha.statistics.usercode with borrowers.categorycode where it is easily available.
Currently for statistics.type 'issue' OR 'localuse' OR 'renew'.

Supplied a script to UPDATE the old statistics records.

Have fun!
Comment 20 Olli-Antti Kivilahti 2014-07-04 08:46:49 UTC
Sorry, we are using some ancient version of master by now (but are looking forward to rebasing :)

Simply attached this patch in hopes of it being usefull.

Looking forward to rebasing into Bug 11230!

> However, I'm concerned with the "DB size" issue... Do you know if creating
> columns in a table but NOT filling them (it could depend on the activation of a 
> syspref) will have a significant effect on the size of the database?

I recall from my lessons that if you set a static size for a column, it always uses that much space. That way searches are super fast.
But I am not a DB specialist.
Varchar columns need a separate map to find in which sector/address they reside in.
Comment 21 Olli-Antti Kivilahti 2014-07-04 08:49:08 UTC
BTW. We could possibly have a separate Database inside Koha's MySQL/MariaDB just for statistics. That could be easily separated from the main DB and put somewhere with lots of hard drive space.

Also that wouldn't complicate Koha installation one bit because we can use same credentials for both DB's by default.
Comment 22 mathieu saby 2014-07-28 08:40:57 UTC
Hello Olli-Anti
Bug 11230 has been pushed, so if you still want to work on that, could you
- assign 7021 to yourself
- refactor your patch
- change the status to need signoff?

I will have some time to sign it before september.


Mathieu
Comment 23 mathieu saby 2014-07-28 08:42:38 UTC
(also, could you provide a test plan for your patch?)
Comment 24 Nicole C. Engard 2015-12-04 16:46:59 UTC
Can someone bring this back to the forefront, it's a big deal if we're anonymizing patron circ history because we lose the category.
Comment 25 Nicole C. Engard 2016-02-03 18:02:22 UTC
I have another example here.  If a child become adult their stats go to adults even though they were children at the time. I'd love to see this patch rebased/fixed for current Koha if possible.

Nicole
Comment 26 Barton Chittenden 2016-03-17 14:57:17 UTC
What's the status on this? The bug report shows 'Assigned', but there's also a patch. Is it ready for sign-off?

Can someone add a test plan?
Comment 27 Nicole C. Engard 2016-05-12 15:54:35 UTC
Checking in here again ... any updates on a test plan?