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