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

[Scott Prater]

OK, thanks.  I'll proceed with these database changes on my
development instance, then.  Let me know if you'd like me to take a
look at the bug.

-- Scott

On Wed, Nov 9, 2011 at 8:28 AM, Thomas Berezansky <tsbere at mvlc.org> wrote:
> 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
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>