--
ScottMaxson - 2012-05-23
ITSB Data Design, Models, Storage Information for various features in the Skill Builder applications
Sub-schemas and notes, specific to various functionality in the itsb application.
Note that the data is split, for legacy reasons between a "360" database used for survey data storage and a "Dev Suggestions" database used to store most user information and the competency/category skills tree, with associated learning resources. There is some duplication of user and category information between these 2 databases (see
this bugzilla bug).
360 Survey Data
Core tables for survey data collection are in the diagram UNSPIRIT_360_CoreSchema.jpg attached below.
There is one flaw (soon? to be fixed) in the diagram relationship between the Category table and ScoreRange table - this relationship is not actively used. Usually the Category table has a dummy or NULL value for ScoreRangeID and the relationship between a survey item and the ScoreRange is maintained in the SurveyItems table instead.
Survey Admin
Though there is a tool for managing some of the data around a 360 Survey in the web application, many tasks have to be accomplished by directly inserting or updating database tables.
Dev-Suggestions
Data for "Developmental Suggestions" is held in a separate database schema (and currently in a completely separate database on production as well). Usually this is referred to as the "DSUGALL" schema. Tables here store the primary user information for logging in, customer info, development plans, and the complete content model of competency/category, behavior, and learning resource(s).
Content Model
See Content Model diagram attached below.
Changes (Fall-2013) made in user accounts and relationships
A few foreign key and unique index changes made to allow users to have multiple "accounts" with the same email address. This fixes a problem we've seen in the past where current, valued customers can't register for special accounts, such as trial accounts, under a different content model. See
DuplicateAccountEmailChanges for details.
Key Learning surveys
Some customers have users complete a short survey on a learning resource when they complete an action item for that resource on their development plan.
- schema diagram showing tables that store key learning survey, and their relationships.: