Statistics: Library attendance

[meteos77]

Goal: Obtain the number of people who visit the library over a one-year period.

• Regulatory constraints: We do not keep member records after books are returned.
  SELECT DISTINCT memberid,reserved_date FROM item_borrower WHERE reserved_date LIKE ('%2025%')

would have been too easy :-)

• Library constraint: Use of SQLITE

I am looking for some ideas to achieve the objective.

[textbrowser]

Don't you mean book_statistics?

[textbrowser]

A cryptographic hash of (member-id || name || ...) stored in book_statistics would indicate the unique people who borrowed that book. And a select on book_statistics would be, as an example:

Digest | Reserved Date | ...
ABC | 2025
ABC | 2025
DDD | 2025
EEE | 2025

[textbrowser]

And a "select count(distinct digest) from book_statistics) where reserved_date like '%2025%'" would produce the desired results.

[meteos77]

What is digest?

[textbrowser]

The problem is the cryptographic digest is of a known value and therefore computing the digests will discover the original members.

[textbrowser]

https://en.wikipedia.org/wiki/Cryptographic_hash_function

[textbrowser]

You'd need to store a key or prompt for one. Then record digest(member-id || ... ||, key).

[textbrowser]

I don't know of another method of recording deterministic values in book_statistics that also do not connect themselves to the original members.

[meteos77]

I'm going to study the regulations further to see if the hash is OK, as the name is no longer clear.

[textbrowser]

https://en.wikipedia.org/wiki/Data_anonymization

[textbrowser]

Solution attached.

[meteos77]

I'm torn between your last solution and not answering the question at all.

• Some people are trying to funnel us into giving them as little information as possible.
• Others are asking us for a megaphone so they can get as much information as possible.

[textbrowser]

Write in book and erase after. Pencil or Pilot erasable pen. Affordable and reusable. Burn book but not in a corrupt file. Shred? Shred better than burn. Or reuse it so no one knows patrons. But no one actually care. So write and keep details and sell them on the US data collection.

[textbrowser]

So all done?