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

[Thomas Berezansky]

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