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
