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

[Scott Prater]

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
>>>>
>>>
>>>
>>>
>>
>
>