Introduction
ServiceNow currently uses MariaDB for a database system, which is an open source fork of MySql. There are some whispers that this may be changing long term to another SQL database system that doesn't have some of the constraints that we will discuss in this future series.
ServiceNow has taken a relational database (more information here) and abstracted to be closer to an Object Oriented Programming model (while keeping many of the aspects of a relational database). This does not change anything about the underlying database structure at all, it is still at its heart a relational database.
This model allows for a table in ServiceNow to inherit from (or extend in platform terms) another table. This makes all of the fields that reside on the base table available to the extended table, without them having to be added again. In addition, you can look at the base table (in the example, task) and you will see all of the records that reside both in task and all of the extended tables, you just won't see all of the columns that have been added to the extended tables.
Physical vs Logical (or Application Model) Tables
One thing to touch on here is the separation or difference between how an application models (or internally uses) a database and a physical database.
The physical database is usually a management system (in this case an RDBMS - Relational Database Management System) that physically stores data. In an RDBMS that data is usually stored in one or more tables, which consist of one or more columns, looking much like a grid of data.
An application internally can utilize these tables in almost any way it sees fit. Many times it will be a direct relationship between data being used in the application and that database, but sometimes (as is the case with ServiceNow) what we see on the Application or User Interface layer can be different from how the database physically stores the data.
In our case, a Physical Table is defined as the physical storage location that data resides within a row and column structure within the database.
A Logical Table is defined as the internal representation of a data model that is usually mapped to physical table structures.
Table Per Class
When ServiceNow was first created, this was the default physical data model that was used to store data in the database. Physical and Logical tables were 1:1, for every logical table we created through the application we would have a physical table in the database.
(In our example) A physical Task table existed along with a physical Case table. When a query was done, the two tables would use a database JOIN operation to bring the two datasets together, to be represented as a single record or database row.
If you create a standalone or new hierarchy of tables, this is still the default mechanism for those tables to be created.
The Problem
As ServiceNow matured, customers would have increased row counts in these tables. It was found that during a query and doing a JOIN operation on large datasets within the task table and extended tables, performance reductions were seen. This would compound as the number of records increased.
The Solution
ServiceNow introduced Table Flattening and Glomming. This was a process (specific to the task table hierarchy, which was by far the most inherited from table set) that collapsed the table structure down into a single table (task), but still looked to the end user as a series of extended tables. Essentially having Logical Tables and Physical Tables.
This works well, as now when querying data from Case, only a single query from a single table needs to be completed on the task table.
ServiceNow saw an issue with doing it this simply though. As tables are created that extend from task, they would create countless numbers of columns on the task table that would only be used by that particular table. Such as adding Incident into the above hierarchy with a (now deprecated) incident_state column. This column would only be used by the Incident table when queried.
MariaDB has restraints built into the engine that limits the overall size of the total columns on a single table. Meaning that a single table in the database can only have a limited number of columns on it before the database will error when doing an Alter.
The Solution to this: Glomming
ServiceNow created a mapping system called Glomming. This uses an alias (or mapping table) called sys_storage_alias that defines the physical columns in the database that logical columns are stored in. This same system is also how ServiceNow accommodates longer column names than are usually allowed in the database management system.
In this example, Case and Incident are separate "hierarchies." This means while they both extend Task and share the columns that the base Logical Task table has defined, the rest of their columns are completely independent of each other. This independence is what allows for their columns to be mapped (or glommed) to the same physical column on the physical table.
In the next segment of ServiceNow Table Structures we will discuss some of the issues and known errors that arise from the database system and glomming.
ServiceNow Support Knowledge References