OK, thanks. I'll proceed with these database changes on my development instance, then. Let me know if you'd like me to take a look at the bug.
-- Scott On Wed, Nov 9, 2011 at 8:28 AM, Thomas Berezansky <[email protected]> wrote: > 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 >>>>>> >>>>> >>>>> >>>>> >>>> >>> >>> >> > >
