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