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

[Scott Prater]

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