[OPEN-ILS-DEV] Feature Proposal: Enhancements to Patron Statistical Categories

[Thomas Berezansky]

In the statistical category editor you get to pick what org unit you  
are working with at pretty much every point you can create an entry  
(stat cat or stat cat entry), provided you have permission.

Although in verifying that I found a bug with editing. :/

Thomas Berezansky
Merrimack Valley Library Consortium


Quoting Scott Prater <sprater at gmail.com>:

> Ah, okay.  That makes it much clearer, thanks.
>
> So how about this for the actor.stat_cat_entry_default table:
>
> new table "actor.stat_cat_entry_default"
> Columns:
> id : serial -- PRIMARY KEY,
> stat_cat_entry : integer -- NOT NULL, REFERENCES actor.stat_cat_entry
> stat_cat : integer -- NOT NULL, REFERENCES actor.stat_cat
> org_unit : integer -- NOT NULL, REFERENCES actor.org_unit
> UNIQUE (stat_cat, org_unit)
>
> And the following stored procedure:
>
> org_unit_stat_cat_default ( org_unit integer, stat_cat integer)
> Language: PLPGSQL
> Return Type:  stat_cat_entry integer (or NULL for none)
>
> How does the org_unit get provisioned?  Is that an attribute available
> in the user session?
>
> thanks,
>
> -- Scott
>
> On Tue, Nov 8, 2011 at 6:30 PM, Thomas Berezansky <tsbere at mvlc.org> wrote:
>> The stat cat owner determines the point the stat cat shows up in the org
>> tree.
>>
>> The stat cat entry owner determines the point the entry shows up in the org
>> tree, and should be equal to or under the stat cat owner.
>>
>> But if you create a consortia-wide stat cat, with 10 consortia-wide entries
>> so that they all show up everywhere, you may want to allow each branch to
>> pick a different default.
>>
>> Because the stat cat *and* the entries are all owned at the same place you
>> need an additional org unit link to allow each branch to pick a different
>> default. This would mean a unique on the combination of org unit and stat
>> cat. And, as an argument against having the unique entry on the stat cat
>> entry, two org units may want to choose the same default.
>>
>> As for -1 compared to null or undef, -1 may technically be a valid id, while
>> null/undef won't be.
>>
>> Thomas Berezansky
>> Merrimack Valley Library Consortium
>>
>>
>> Quoting Scott Prater <sprater at gmail.com>:
>>
>>> Both stat_cat and stat_cat_entry have the owner field, which points to
>>> an org_unit.  Isn't that enough to identify the org_unit for a given
>>> stat_cat and stat_cat_entry?  A stat_cat and stat_cat_entry can only
>>> have one owner, so having a unique org_unit in the default table seems
>>> to only introduce extra noise, without adding any extra information.
>>>
>>> You're right, the stat_cat_entry doesn't have to be unique.  But it
>>> would be good to avoid having the same stat_cat_entry listed as
>>> default multiple times in the table.
>>>
>>> One niggling doubt I have about the new stat_cat_entry_default table
>>> is that we assume the stat_cat_entry is linked to the stat_cat, but
>>> there's nothing in the table schema itself to enforce that relation.
>>> You could conceivably have a stat_cat_entry that links to a completely
>>> different stat_cat than the one listed in the stat_cat field.  I left
>>> it, though,  since I noticed other tables have similar implied, but
>>> not enforced, relations.
>>>
>>> I decided on "-1" instead of null, since I figured the return type
>>> would be an integer (the default stat_cat_entry ID).  Seemed a little
>>> bit cleaner that way, but I don't have a strong opinion on that.
>>>
>>> -- Scott
>>>
>>> On Tue, Nov 8, 2011 at 5:23 PM, Thomas Berezansky <tsbere at mvlc.org> wrote:
>>>>
>>>> For the default you will want an org unit involved in the table.
>>>>
>>>> The uniqueness would be org unit and stat cat, the entry doesn't need to
>>>> be
>>>> unique at all.
>>>>
>>>> A stored procedure to get the default given an org unit and stat cat
>>>> could
>>>> be useful, or it could be done at the perl layer. Either way it would be
>>>> "walking up the org tree" to find the default. Returning null/undef would
>>>> be
>>>> a decent way to say "we don't have a default for that stat cat/org unit
>>>> combo", I think.
>>>>
>>>> Thomas Berezansky
>>>> Merrimack Valley Library Consortium
>>>>
>>>>
>>>> Quoting Scott Prater <sprater at gmail.com>:
>>>>
>>>>> Thomas (and anyone else),
>>>>>
>>>>> Here's my proposal for changes to the actor database schema for the
>>>>> patron statistical category enhancements:
>>>>>
>>>>> 1.  Mark a required category
>>>>>
>>>>> table actor.stat_cat:
>>>>> new column "required":  boolean -- NOT NULL, DEFAULT false
>>>>>
>>>>> 2.  Allow/disallow user-entered text for a category
>>>>>
>>>>> table actor.stat_cat:
>>>>> new_column "allow_freetext":  boolean -- NOT NULL, DEFAULT true
>>>>>
>>>>> 3. Set a default entry
>>>>>
>>>>> new table "actor.stat_cat_entry_default"
>>>>> Columns:
>>>>> id : serial -- PRIMARY KEY,
>>>>> stat_cat_entry : integer -- UNIQUE#1, NOT NULL, REFERENCES
>>>>> actor.stat_cat_entry
>>>>> stat_cat : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.stat_cat
>>>>>
>>>>> By including both stat_cat_entry and stat_cat in this last table and
>>>>> declaring each one as UNIQUE, we can ensure that for a given
>>>>> statistical category only one entry is ever marked the default.
>>>>>
>>>>> Would it make sense to create a stored procedure that returned either
>>>>> -1 (no default entry) or the ID of the default entry for a given
>>>>> stat_cat?
>>>>>
>>>>> How does this sound?  Am I missing anything?  Do other schemas come into
>>>>> play?
>>>>>
>>>>> thanks,
>>>>>
>>>>> -- Scott
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>