Understanding Storage Aliasing for Task


Description

Overview


This KB will provide information on what a storage alias is and how storage aliases are used in ServiceNOW specifically for the Task table. By default all Admins have access to the storage alias table in an instance (sys_storage_alias) ,however any transactional process against this table cannot be performed by an Admin User from the user interface. The purpose of this KB is to help admin/users understand the critical role that storage aliases play when manipulating data or creating new fields on a table.

 

Table Hierarchy

To understand the ins and outs of storage aliasing it is important to understand table hierarchies within the task table. There are two models at play on the task table which consists of Table Per Hierarchy (TPH) and Table Per Class (TPC).

TPH - In this table model we have one physical table 'task.' TPH has a flattened hierarchy where all columns which exist within the task hierarchy will exist only on the task table (ie. change request fields do not exist on the change_request table but on the task table as there is no physical change_request table in the backend DB). To determine if a table in the "task" hierarchy is TPH, and thus stored in the physical "task" table, find the parent of the table that is a direct child of "task".  Then look in sys_db_object.list for the "extension_model" field for that table.  (You may need to add this field to your list or form view to be able to see it). If the field is empty the table is TPH.

A simple rule of thumb to follow when viewing table hierarchies is that an extended table should inherit the hierarchy of it's parent (i.e change_request_imac is a child table of the change_request table which extends task. Since change_request is TPH change_request_imac would then also be TPH). Legacy tables such as incident, change_request, problem, etc are all part of the flattened task table hierarchy.

TPC - In this table model we can describe tables within the hierarchy as the tables do exist physically in the database. Glomming (will be discussed in further detail below) however does not apply here as this is only used in a flattened table hierarchy. Starting with Geneva any new direct descendant of task that is created with the condition that the task row count is > 1 million rows will be created as TPC. This is only true for new tables which extend the Task table directly. 

 

What is a storage alias?

A storage alias entry is created for every single field created on a table within an instance. Before getting into what purposes storage aliases serve there are a few important fields that admin/users should know of:

Element Name - This value reflects how the field looks like from the front end. i.e. if a user were to look at the column_name field in sys_dictionary or if a user/admin were to write a script to manipulate values in a field they would base this off of element name.

Storage Alias - This value tells us exactly where the data for a particular element is stored. When fields are being manipulated from the backend database, the platform looks at the storage_alias value to understand what data should be manipulated based off of the storage alias value as well as the sys_class_name which is the actual value of the table class where data is being manipulated. The storage alias value is the actual physical column on the task table.

Storage table name - This value tells us what physical table the element is part of. For all logical elements in task, the storage table name will always be task. If this is a TPC table the storage table name value will be the name of the physical table where the physical element lives.

Example Records

The example list view provided should paint a clear picture how data on task within a flattened hierarchy is manipulated.Looking at the very first element in the list 'category' we see that the storage_alias value is set to a_ref_2 and the storage table name (physical storage table where the data lives) is in fact on the task table. Additionally, we see that there are a total of 9  logical elements in different logical classes of task which all link to the same value a_ref_2 on the physical storage table task. This is an example of 'glomming' where multiple sibling elements can share one physical column on the task table. If a user were to query data from the logical element 'category' the query would look something like 'SELECT a_ref_2 from task WHERE sys_class_name='std_change_proposal' AND a_ref_2 IS NOT NULL.

 

In the select query we are specifying data that lives in the physical column a_ref_2, but to ensure we are pulling the correct data we use a class discriminator. The class discriminator value and the storage_alias value lets the system know that we are looking at the logical element 'category' from the logical class std_change_proposal on the physical table task.

The naming convention for fields created in the actual physical tables can vary depending on the type of field being created. In the example above 'a_ref_2' is an alias that has been created on task to hold values for references type fields.

 

What are storage alias records used for?

1. Mapping logical TPH)/physical (TPC) elements to actual physical columns in the backend database.
    In simple terms, this means it tells us where the actual data in the element lives from the column in the physical storage tables.

2. Enable multiple sibling elements to share one physical column (also known as glomming but only for TPH)

3. Mapping sys_documentation (label) records to their respective elements which is what users/admins see from the application front end on forms, reports, and list views.

 

Additional things to know

Two logical elements within the same logical class can never share the same physical column (i.e. If a user created two string fields on incident they would not map to the same physical column in the database).

- A parent element and a child element can never share the same physical column (ie. If a field is created on incident it cannot be mapped to a physical column where a field on task is already using the physical column).

- Only sibling elements can share the same physical column (i.e A user can create a reference field on change_request and incident and map to the same physical column).

- If a field is created directly on the task table (where sys_class_name is task) it can never be glommed.