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
