Table Extensions - Table per class, Table per hierarchy, Table per partition - Further Clarification

[SB87]

Hi all,

I have read the following docs:

Table extension and classes (servicenow.com)

But I'm still have difficulty interpreting this based on the data I've viewed.

I'm aware the [task] table is of type Table per hierarchy and the docs says the following: "Creates one database table for the parent class, which stores all records for the parent and child classes. Child classes do not have separate database tables." 

Using [incident] which extends [task] as an example:

• When viewing an [incident] record using RestAPI Explorer, 147 records are returned.
• When viewing a [task] record using RestAPI Explorer, filtering 107 records are returned.
• When the column is shared, the values are identical.

Now, I am able to analyse the above by retrieving the [incident].sys_id and then adding a condition on the [task] table to return the [task] record that relates to this incident that has the same sys_id.

What's confusing me is the following statement in the above link, under the "Tables Created" section. It states the following: Creates one database table for the parent class, which stores all records for the parent and child classes. Child classes do not have separate database tables.

In RestAPI Explorer I can see separate tables are listed for both [incident] and [task], but I presume the [incident] table is just an impression where the UI makes it appear the [incident] table is separate, but the data itself is coming directly from the [task] table where the "sys_class_name" = "Incident". 

If so, I guess my confusion is in trying to visualise this based on the design. My understanding is that under the hood of ServiceNow sits a MariaDB relational database. Knowing the [task] table is extended by others, like, change_request, problem, sc_request - how does this work if the columns specific to these tables belong to the [task] table and where there isn't a physical table for these children?

For other tables that uses Table per class, the docs appear to be conflicting (in my opinion) following the above. It states the following-

Tables created:

Creates a separate database table for the parent class and each child class.

Records replicated:

The parent class replicates each record stored in its child classes. Each child class only stores records unique to the class. Replicated records have the same Sys ID value in each table. 

Isn't this the same as Table per hierarchy where I can filter on [incident] records in the [task] table using the shared sys_id?

I suppose my underlying question is the following:

1. When the table type is Table per hierarchy, if we are extracting both the parent and child tables via REST and if we wanted to view everything for, say, an Incident record, then is it feasible to view the fields unique to [incident] from the [incident] table and to establish a relationship to the [task] table using the sys_id to retrieve the fields that are common in the parent [task] table?

If not, can you please explain?

[Amit Gujarathi]

HI @SB87 ,
I trust you are doing great.

Your understanding of ServiceNow's Table per Hierarchy (TPH) model and its implementation in the context of the task and incident tables is on the right track, but there are some nuances that might help clarify your confusion.

In ServiceNow, when a table like incident extends task, it doesn't create a separate physical table in the database for incident. Instead, all records for incident are stored in the task table. The way ServiceNow distinguishes between task records and incident records is through the sys_class_name field. This field stores the name of the class (or table) that each record belongs to. So, an incident record in the task table will have its sys_class_name set to "Incident".

[SB87]

Thanks @Amit Gujarathi - this seems to align to the details in my post. So when calling the [incident] table using RestAPI explorer in ServiceNow, I presume all it is doing is returning records from the [task] table where sys_class_name = "incident" - is that right?

Is there anything I need to worry about for tables that are Table per partition? I understand this is mostly used in the CMDB area, but what I have found is that I am able to trace the sys_id all the way up the hierarchy tree to the [cmdb] table.

For example, [cmdb_ci_netgear] has the following table hierarchy:

• cmdb_ci_netgear --> cmdb_ci_hardware --> cmdb_ci --> cmdb

If I was to get the "sys_id" from [cmdb_ci_netgear] I can return records in all other tables for which it extends in its hierarchy.

To give you some background as to why I am asking these questions:

We are building a data warehouse (there is a business need, and the details I can't share) that extracts data from various tables that have been identified and considered useful from ServiceNow, using ServiceNow Database Views. The views have been configured to only add fields that are explicit and not inherited from an extended table in the hierarchy. We then call these views via REST to extract data from them.

We then trace the records back through the table hierarchy rather than duplicate fields e.g. for incident, we can source the views from the [incident] view and those that belong to [task] are traced back to the [task] view via the sys_id. We plan to use the same approach for all other tables, including cmdb.  

So, for example,

• [cmdb_ci_netgear] will be a ServiceNow view for which we extract. This will only contain fields specific to this table i.e. not derived from a parent in the hierarchy.
• The same goes for cmdb_ci_hardware, cmdb_ci, and cmdb.
• If we wanted to view a cmdb_ci_netgear record, we would return those specific to this view and then trace back to other tables in the heirachy for other fields and values.

Is there any dangers or things to consider in this approach, given the Table per class, Table per hierarchy, Table per partition differences?

Many thanks.