Base table vs child table, and table extension models

[Singularity]

Hi,

Demand (dmn_demand) is extended from Task.

Project (pm_project) is extended from planned_task, which is extended from Task.

Let's say you need a new field for both demands and projects - but this field would serve no use on anything else (e.g. incident, problem, change have zero use for this field).

- Do you add this new field to the Task table and keep it hidden on incident & other tables, or do you add it only to the child tables dmn_demand and pm_project? What would be a best practice in this scenario? What rules do you follow in your projects, do you worry about polluting Task with columns that are only relevant for PPM and not ITSM?

- In an ideal world, I assume both demand and project would extend from planned_task, and you would add the field to planned_task, however this is not the case.

Also, if I understand TPC and TPH (Task) and TPP (cmdb) table extension concepts, from database perspective or performance perspective it actually does not matter where you put the new field - whether it's on a child or on a parent table.

[-O-]

I tend to create all new fields on Task. That is because if you define the same field on two extensions, you get two "real" DB fields:

So defining it on Task saves you one "real" DB field should it need to ever be reused on an extension. If it is not used on any of the extensions, no harm done - it is the same.

Also if you define a field on an extension you can no longer define it on the root Task. So if you later find out that you need the same field on several tables you have to either name it differently on each table define the same field on those extensions (also wasting real DB field real estate - which is not infinite, but bound by the underlying MySQL/MariaDB table size limitations), or do a complicated dance where you back up the data into a temporary backup field, drop the column from the extension table, create the new field on Task, move back the data where appropriate. You have to do all this keeping in mind that you can't move the removal and re-creation of the field in the same update set.

[Singularity]

Thanks! Useful tips.

One question: What do you mean "real" DB field?

My understanding is that the table extension model of Task in the latest versions is TPH. Table per hierarchy in my understanding would mean there is a giant Task table and every record is within Task table. My understanding is that no matter where you add the new field, whether to the parent or child table, it will go to the same place in MySQL in the end - the Task. So from DB perspective, it is essentially the same.

In below table you can see short description is a shared field, the other two are not (security incident and problem workaround).

Number	Short Description	Security Incident	Problem Workaround	Task Type
INC001	Virus on ABC server	TRUE	NULL	Incident
PRB001	XYZ server keeps going down	NULL	Restart	Problem

Is this understanding correct or am I missing something?

[-O-]

Yes, that is my understanding also - there is a giant Task table.

By "real" DB field name (not simply field) I mean that name of the column as exists in the underlying MariaDB database. While OOB field names mostly are the same (what is visible in SN and what is for real in the MariaDB database), in case of custom fields those never match. What one sees in SN is whatever you have defined, but in the underlying MariaDB database it is a name that starts with prefix a_ is followed by a data type code (e.g. str), followed by an index number prefixed by underscore.