Extending sys_user - Don't Do It

Great point, Nia! I am currently involved in a costly migration of a custom table out of the sys_user hierarchy. Certainly the reasons you posted are great reasons. In addition, the situation that I am working on involves an implementation that results in about 1 million "users" added per month. This has its own set of performance implications. The queries triggered during usage of reference fields on the sys_user table are getting unwieldy at around 11 million records. I've seen similar performance issues on sys_user at smaller sizes and have listed a few of the things I've seen in my recent article series "Performance Hacks: Reference Fields"

Performance Hacks: Reference Fields (1 of 4) Reference Popup / Reference Lookup

Please 👍 if Helpful

"Simplicity does not precede complexity, but follows it"

I'm sure there are other reasons to not extend sys_user. Could there be other problems with doing so? I am trying to make the argument to my team and make it a best practice. There maybe cases that avoid the problem with the username being a unique field. 

What about vendor contacts in GRC? Why are classes ok but not extensions of the user table. 

There are issues i came across with too

a) If the user record is already present in sys_user then the corresponding record cannot be created in the extended table. For this either the userid has to be kept as email or employee no in the extended table.

b) On top of it we see 2 records - looking from sys_user table.

An article existed somewhere it was glad to find.

Cheers!!

Anu

Thank you @Nia McCash  I know when we worked through this, there wasn't anything on it and the design of some of the CSM tables can lead us astray as developers. I appreciate you putting some information about this out there!

I am currently working on an onboarding solution. For our client, applicants being onboarded will not be allowed to log in to use the system until they are officially hired. For this reason, we are extending sys_user to create an Applicant table. This way, we can track them as the 'subject person' in the onboarding HR Service field to comply with intended OOB functionality. When the applicant is hired, we will update their Applicant record Task Type field to move them up to the sys_user table. Using a separate Applicant table (not extended from task) and not having the audit trail in the intended subject person field (since this references sys_user) is a clunky alternative.

While I do agree that extending from sys_user is almost always a bad idea, I think there are a limited number of use cases (for example, this one I mention here) where this is best approach.

@cmayfield That was the exact use case that we were asking about extending the user table for. The problem though is how did you account for one person existing on both the user table and the candidate/applicant table since that is what extending the table does? 

@jxsaxton421 In my mind, the candidate / applicant would only exist in one table at a time, technically.

Since HR Services require a subject person which references the sys_user table, if you extend from sys_user for candidate and create a record there (so far only one candidate record, however if you show a list of all sys_user records without filtering on task type, because this is the parent, you will see the candidate record), you can link the candidate to the HR service (onboarding).

Then, when the candidate becomes an employee/contractor, instead of creating another user profile record, you take the existing candidate record, change the task type field (this is the field that defines which actual table the record lives on and since the inheritance concept is in play, it can be a bit confusing) and now that record is on the sys_user table only and no longer in the candidate (child table). The HR service subject person link is not broken and the now employee can be linked to their initial onboarding record without data loss or duplication.

Did I answer your question?

The problem is the way ServiceNow designed extending tables --- your employee if they were hired as a candidate would still also exist on the candidate table. Changing the task type field I thought was difficult anyway? 

We had come up with the same approach of changing the task type field, but the above discussion came from people at ServiceNow who also said they had to tell the people who work on CSM not to do that because CSM has done it that way. I hope they issue some guidance or more details on why this is considered a bad practice. The simple solution makes a lot of sense and I think everyone is going to do it.  

@jxsaxton421 If you change the task type field for a record (this is easily done with a server script), then you redefine the table that the record lives on. It gets confusing because the extended table is technically a child of whichever table it was extended from. This means if you view a list of all records for the parent table, in our example 'sys_user', and you don't filter by task type, you would see the applicant record. The same is true if you view a list of all task records. This list will include all incident, case, change etc. records, since all of these tables extend from task.

@christinemayfld While changing the sys_class_name (Task type) of record wouldn't there be chances of data loss if the extended table contains additional fields?

@Sandeep Rajput good point. Yes, any data in fields that exist on the child applicant table that do not exist on the parent sys_user table would be lost when you move the record (change the task type field value).

@christinemayfld, @Sandeep Rajput, regarding changing class of the record, I'm afraid that it's not only about loss of additional fields, actually whole record can be lost. When class is changed, first, record deleted from one table and then inserted to another. During 2nd step (insert)  it could be that some mandatory data is missing (due to different mandatory, data policy configs for 2 classes) and insert will be aborted. 
At the end we will have deleted record for 1st class and failed insert for 2nd class. 

More details in KB0727652.