Task table flattening

Within the platform, a logical table, such as the task list, represents one or more physical tables in a relational database.

Hierarchically related tables, such as Task [task], Incident [incident], Problem [problem], and Change [change_request], can be stored in one of two different extension models. The Table per class extension model stores hierarchically related tables as separate physical tables. The Table per hierarchy extension model stores hierarchically related logical tables as a single, flat, physical table and provides a performance improvement over multiple, joined tables.

The instance uses the table per hierarchy extension model for the Task table on MySQL databases. Other tables use the table per class extension model because there is no performance benefit to flattening them. To use table per hierarchy on an Oracle database, contact Technical Support.

Advantages of task table flattening

The instance uses table joins to associate hierarchically related tables in the Table per class model. Queries against any child table, such as Incident, must join the child table to the parent table, such as Task. These table joins cause a performance bottleneck when performing queries. This bottleneck is especially noticeable when a query accesses fields from multiple child tables, such as when querying both Incident and Problem table values.

A flat table hierarchy allows the instance to access the data across these logical tables without first joining them because the data is stored in the same physical table. Flattening a table hierarchy does not affect the appearance or functionality of tables as they appear in the instance. All table functionality, including database views, remains unchanged by flattening the table hierarchy.