Introduction
This document will introduce the design principles and overall structure of the schema for staged data. It will be largely agnostic to the version of core, though it will assume 7.9 as the ‘applicable from’.
The data sets that can be loaded fall into two taxonomies:
- Relating to the Operational Data Store (aka ODS) - organisations, people, contact methods, etc.
- Relating to work item instance data - matters, instructions, tasks, etc.
Within each taxonomy, there are tables that act as primary containers and those that are implicitly for child data. For example, organisation and matters are primary tables, while address information and phase history are child data of each.
Common Patterns
All tables in the schema have a primary key named reference, which may be a computed column.
The concept of matching data is to avoid creating duplicates if the data already exists, whilst allowing the loading of new data that refers to it. For example, if a given organisation already exists in the environment, and we wish to data load contact details (child data) we need to match the org against the existing. There are two methods to match - by providing the internal guid of the item and by matching of fields as a (logical) key.
The internal GUID would be non-trivial to determine via the UI, but IS available in the reporting data warehouse. The method of matching on values varies by the dataset, examples include:
- Users can be matched on identity claim + identity provider
- Matters can be matched on reference
- Organisations can be matched on external reference
Please get in touch to discuss these concepts and techniques in greater depth.
All tables are in a schema called source; any other schema and tables in the staging database that ShareDo connects to will be ignored. The use of the schema name source is omitted below for brevity.
Configuration Tables
Alongside the tables holding staged data - the source schema - a second schema may exist that holds a configuration extract. This schema has no direct role in the execution of the Load tools (thinking in ETL terms). However, the team that is producing the Extract and Translate steps may benefit from a form of the configuration expressed in regular tables. This extract is taken from any environment that has current configuration - typically a DEV or UAT type environment. This config extract is into a set of tables in a schema called importConfig. As of core 7.9.0, the list of tables is:
- contactTypes - contact methods such as mobile phone, direct line, Twitter, CompuServe, etc.
- countries - full country name with ISO codes, note that the 3-character codes are used for addresses
- formsWithFields - a simple extract of every field and the form it is on, alongside some metadata
- fieldsWithOptions - for every field of type optionSet, the list of valid values
- keyDateUsage - the join from configuration between key dates and ShareDo types, to express which are allowed
- odsLocationTypes
- odsTypes - note this should be called odsTags, holds the tags or party types that can be used in
source.odsTags. - optionSets - a simple list of the option set lists in the environment
- optionSetValues - for every option set list, a simple list of all values, including attributes such as parent item (if it's hierarchical) and meaning code
- participantRoles - every role and attributes for if the role can be held by Person, Organisation, User or Team (and combinations thereof)
- phases - simple list of all phases with the phase plan and the belong to and ShareDo type they relate to
- ShareDoTypeForm - the join of which forms have been configured (as aspects) against ShareDo types
- ShareDoTypes - an extract of every ShareDo type and its primary attributes
- ShareDoTypesCategories - for every ShareDo type that has a category list, a list of all of the options. May be referred to as subtype or categories
- ShareDoTypesParticipantRoles - every configured combination of ShareDo types and participants
- These tables don't reflect any inheritance that applies and can be seen within Modeller. For example, a participant role may be configured on a matter type and implicitly apply to all child types.
- These tables don't reflect the configuration that would be used, merely that it exists. For example, the list of option sets will include many that are part of the ‘out of the box’ data rather than configured for a client.
- This list will expand over time as clients & partners identify other key areas that would benefit in being extracted into these ‘stand alone’ tables to aid in the ET of ETL.
Glossary of Terms
| Term | Meaning |
|---|---|
| ODS | Operational Data Store - long lived, cross case data including organisations, people, addresses, contact methods etc. |
| ods tag | An attribute of an ODS, applies globally to that ODS irrespective of if or how the ODS is used as a participant in a role |
| role | The use of an ODS on a work item with a specific role, combinations of work item types are managed within modeller. |
| ods type | The intrinsic type of the data - organisation, person, user, team |
| ShareDo | The internal name synonymous with work item |
List Of Tables
The list below includes a one-line summary. Please refer to the Schema Tables article that documents each table, its purpose and key use considerations in detail.
Base Tables
- ods - one row for each row in the primary ods tables
- ShareDo - one row for each row in a primary ShareDo table
ODS Primary data tables
- organisation - shared primary key into [ods]
- person - shared primary key into [ods]
- team - shared primary key into [ods]
- users - shared primary key into [ods], extends the details in [person] with user attributes such as identity claim
ODS Child Data
- address - address, geo code, DX, etc.
- contactDetails - all types, including email, phone numbers, Twitter, CompuServe, etc.
- employmentDetails - specific metadata to connect organisations with people as employees
- formAspectAttributes - allows loading of ODS fields as extended via aspects & forms in modeller
- odsRelationship - to link two ODS entities in relations that can be extended in Modeller
ShareDo Primary Tables
- contract - within typical ShareDo deployments, the top-level item
- instruction - optional, may relate to a matter, typically captures enquiry-type data
- matter - the most common entity used to match ‘a thing’ that is progressed in the business context
- proceeding - optional data within a matter holding proceeding-specific attributes (such as jurisdiction and court track)
- statementOfWork - within typical ShareDo deployments, data that is a child of a contract and a parent to matters
ShareDo Child Tables
- accountAdjustments & accountAdjustmentTransactions - financial data
- budgetTransactions - similar to reserves, financial data
- comments - free format plan, HTML or markup text
- documents - metadata (only) to enable a deep link from Chronology to a specific file inside the DMS
- formAspectAttributes - allows loading participant and ShareDo fields as extended via aspects & forms in modeller
- incident - within a legal context, extends the entity of a location with additional attributes such as weather conditions
- invoices & invoiceReceiptItems & invoiceTransactionItems - finance data, can be both invoices raised for payment and to reflect received money
- keyDates - structured date fields that have various additional attributes
- legalProperties - within a legal context, extends the entity of a location with additional attributes such as title number
- offers & offerAmounts - finance data, can be both offers made and offers received, with corresponding different associated data in the participant roles table
- participantRelationships - allows a connection to be expressed between participants on a matter, such as ‘claimant is represented by’, useful with many claimants & solicitors within a single matter
- participantRoles - the simple join between a ShareDo and an ods with a role to indicate the what, who, and why
- payments & paymentRequestAmounts - finance data capture payments received and the amounts
- phaseHistory - phase transition data for the ShareDo, should have phases valid for the ShareDo type, but don't need to follow the configured phase plan
- tasks - either generic tasks or specific task types with additional attributes such as due date
- timeEntries & timeEntrySegments - structured time data with attributes such as billing details and time codes