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 <[email protected]>:
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 <[email protected]> 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 <[email protected]>:
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 <[email protected]> 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 <[email protected]>:
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
