-- 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.:
    DEVSUGALL_SURVEY_2.jpg
Topic attachments
I Attachment History Action Size Date Who Comment
JPEGjpg DEVSUGALL_SURVEY_2.jpg r1 manage 427.2 K 2012-05-23 - 19:07 ScottMaxson schema diagram showing tables that store key learning survey, and their relationships.
PNGpng DSUGALL_Content_Model.png r1 manage 183.5 K 2014-12-11 - 23:55 ScottMaxson Content Model tables in DSUGALL schema
JPEGjpg July2014_Survey360CollectionERD.jpg r1 manage 378.8 K 2014-11-20 - 17:57 ScottMaxson Sub-schema for prod01 survey data collection (revised July-2014)
JPEGjpg UNSPIRIT_360_CoreSchema.jpg r1 manage 643.0 K 2014-12-02 - 18:49 ScottMaxson Core 360 Survey Tables
Topic revision: r7 - 2015-06-29 - ScottMaxson
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback