T_DEFAULTS Auto-documentation Table
The Problem
T_DEFAULTS is a critically important, but unwieldy and difficult to manage configuration table. The large number of fields that it handles, along with their duplication over multiple organizations, with few controls over the structure, means that it is easy to make mistakes, overlook missing items or misconfigurations, or misunderstand how configurations are being used. Documentation is necessarily broken into multiple sections and even pages, though these only provide partial information. Misconfigured or missing T_DEFAULTS settings could easily be the single most common source of support tickets for our organization and they have certainly been frequent and recurring project derailments for our various upgrades over the years. With our organic expansion into being a consortium, many of the issues with T_DEFAULTS have become magnified with the addition of each consortium member, and additional confusion has been added by the various integrations we have compiled over the years that have added organizations and sometimes custom fields into the mix.
A Self-Documenting Table
Instead of running back and forth to the documentation pages (and multiple documentation sites when working through an upgrade), I think it would be best to have a table in Tessitura itself that would maintain the documentation text along with flags and labels to better identify and sort the various fields available in T_DEFAULTS. This table could then be joined to T_DEFAULTS in a view to provide non-editable columns to the display in the Reference Tables interface. This would provide consistent documentation at a glance, and could also drive triggers preventing certain illegal configurations, call out custom fields clearly, allow new Organizations to be spun up with their T_DEFAULTS entries automatically created, and support data integrity reports on T_DEFAULTS settings.
Currently T_DEFAULTS lacks useful display or management of the following import features of fields:
Tessitura Standard or Custom
There’s nothing in T_DEFAULTS to let you know if a particular field is part of Tessitura or connected to a Customization, Integration, or Tessitura-built but non-standard feature. In theory T_DEFAULTS should never have custom fields, and VRS_CUSTOM_DEFAULTS should be used for this purpose, but VRS_CUSTOM_DEFAULTS is actually poorly designed for many applications and Tessitura themselves violate this rule constantly. For the foreseeable future this will be a feature that will have to be supported.
Required or Optional
Some Tessitura processes will fail if certain fields are not configured or configured within parameters, others are optional, or have shadow “default” values that the application will use if the field is not found. Again, at the current time, this can only be ascertained by going from field to field in the documentation and reading through the field’s purpose text carefully.
Usage
T_DEFAULTS contains a column called “default_value_desc”, which is used ostensibly as an inline documentation column, but it is not completely helpful. It is short, 255 characters, which is much shorter than almost every description in the documentation. It is also incumbent upon the administrator to enter this text when adding every field to the table. As a result, many fields will have inconsistent text between organizations, or even lack a description altogether (very common). The very name of the column suggests that the documentation is actually supposed to be specific to the entry in question rather than to the field in general.
Organization Configurable or System-wide
Some fields can be configured separately for different organizations, others are supposed to only be applied to the “Impresario” Organization. This information is completely unavailable in T_DEFAULTS, and prone to creating confusion, although I believe I recall that in some cases adding additional field entries can even cause errors.
“Web” Specific Default
Other fields are “Web” or “API” specific. These do not need an “Impresario” linked entry, but should have a “Tessitura Web” entry and are typically only used by TNEW or certain custom websites. T_DEFAULTS does not mark this.
Deprecation
In theory the administrator is to use the “inactive” column to flag deprecated fields, but that gets used for other purposes, and the administrator has to review the documentation periodically to discover when fields are deprecated.
The Table
This is the Table I propose:
TR_DEFAULTS_DOCUMENTATION
- id (int, identity)
- field_name (varchar(30))
- field_name_compute (varchar(30))
- purpose (varchar(1000))
- where_used (varchar(100))
- value_default (varchar(255))
- is_web (char(1))
- allow_org (char(1))
- is_deprecated (char(1))
- version_added (varchar(30))
- version_deprecated (varchar(30))
How These Fields Support the Identified Problems
FIELD_NAME and FIELD_NAME_COMPUTE
Just the name (and underscore/space replacement) for matching. V16 clearly wants to move towards underscore connected field names, but I know from experience that changing current names can break things in v15 at least…
PURPOSE
This is the full documentation on the field, mostly in line with what is available in the documentation pages under the title of “Purpose” today. I observe that this text often contains things like addition/deprecation versions, web or organization, and implicit or example values, which could be dropped from here in favor of their dedicated columns. This would replace the messy and user-entered default_value_desc in the table display, unless it is decided that there is some value in having a place for administrators to make notes about values selected for a specific field entry.
WHERE_USED
For quick search or sort of values: taken from the documentation pages information under the same title, but of course we would want it to be complete and consistent.
VALUE_DEFAULT
If the field is “optional” this would be the value that will be used if it is not configured, but could also be used if triggers are used to enforce the existence of all defined fields to fill the value column. For other fields it might contain a brief example value or value range.
IS_WEB
Defines a field as being a “Web Default”, with “Tessitura Web” as the required and fallback Organization. Beyond integrity checking and documentation this would be useful when adding a new “Web” Organization, especially when scripting.
ALLOW_ORG
Defines whether a field may be assigned a user-created Organization, or if only one value may be set for a Tessitura installation, with the Organization restricted to “Impresario”. Beyond integrity checking and documentation this would be useful when adding a new non-”Web” Organization, especially when scripting.
IS_DEPRECATED
Flags that a field is deprecated in the current version of Tessitura. Documents their status in the table and possibly prevents the addition of new fields of that type.
VERSION_ADDED
This is a well-formatted version number string (i.e. X.Y.Z with the possibility of a space and some additional text afterward). Apart from recording the history of T_DEFAULTS it would be used to determine if a field needs review because a review didn’t happen at that upgrade, whether a field is new with the most recent version, and very usefully if the marked version is greater than the current version then it defines a field that will be introduced in the future, allowing the administrator to review and possibly pre-configure it in advance of the upgrade to that version.
VERSION_DEPRECATED
A well-formatted version number string showing the version number where the field was deprecated. Similar to VERSION_ADDED above, this can be set in advance of the deprecation to alert administrators of the change in upcoming versions.
New T_DEFAULTS
With this table to support it, T_DEFAULTS would now only need to be organization_id, field_name (optionally field_name_compute) and default_value.
(I would understand parent_table and parent_table_compute if the column expected occasionally to have free form names in the past and wanted to continue to support that, but parent_table is a foreign key on TR_ORGANIZATION.description, so that’s not the case. Custom utilities and views should use the supplied functions for lookup, so just change it already! And T_METUSER.location.)
Custom Defaults
A matching table called LTR_DEFAULTS_DOCUMENTATION could be used to allow user inputted values for custom defaults, or they could simply display without the documentation columns. If the table exists then its match would drive a custom flag column in T_DEFAULTS. A trigger would prevent it from allowing field names that conflicted with standard field names. I favor this as I think custom utility builders and integrators are going to want configuration values that can be pulled differently depending on the context the utility/integration is operating under without having to somehow create a different name or Custom Default Category and then build the system to choose the correct one.