When to extend core tables in HRSD

[Jordan Mccann2]

Hello,

Has anyone else encountered an issue where too many custom fields are being added to the core HRSD tables?

My customer's core tables have 200+ custom fields for various HR Services. This has led to many UI policies hiding these fields from other HR services on the same table. It has become a chore to manage these fields, and all the UI policies and ACLs created. Usually, I would suggest having a "core" table be used for all "shared" fields and then extending that core table for use cases where many custom fields are needed. This would allow anyone who needs those custom fields to use the extended table, while anyone who doesn't can use the core table.

If I have the benefits table and I need to add 100 services to that table, and each service wants two custom fields, and let us assume none of the custom fields can be shared, then I now have 200 custom fields on that table with potentially 100-200 or more UI policies and client scripts. Do you know if this is the intended approach, or is there a better way to approach this problem? As it is currently built, I can't see how the COE model doesn't become a mess and is almost impossible to maintain. 

I was not the customer when all this was implemented, and all the custom fields were added. Let us assume all the fields have a valid and critical business need tied to them. I know normally, I would push back on this much customization, but that ship has already sailed. 

My initial thoughts would be to extend the COE tables in a meaningful way to make maintaining these tables easier, but I have read in other posts that this might have unexpected behavior.

Thank you,

Jordan

[Susan Britt]

I would caution against extending the tables to create new ones, as well as the number of custom fields on the tables. There are maximum number of custom field and custom tables that can be created within HRSD. Talk to your ServiceNow account executives about your specific license SKUs to verify. You may have App Engine that will take over where this doesn’t come into play. 

If there’s a true business need other than reporting, then sounds like the custom fields are needed. I would add them only to the COE table they are applicable for if very specific to the service.  

Are the custom ACL necessary for the fields, or can they use the OOB security on the table? In regards to the UI Policies, as long as naming them more strategically, it shouldn’t be too hard to manage. For example, I like to name by the condition (e.g., HR Service=Benefits Inquiry), so later in more fields are added, you can easily identify which UI policy needs to be updated without sorting through all of them.

[Jordan Mccann2]

Thank you, Susan.

My question is more about the architecture and data model surrounding HRSD. This model makes sense until you use it. There is no way to prevent HR from needing additional fields to help manage different services. This is why we can extend tables. I am not worried about the table restrictions. I am worried that COE tables do not scale very well since you are trying to jam everything into this very limited data model.

Let's say we can have an infinite amount of HR services and each HR service could need custom fields. We do our job and ask hard questions and each field is unique and needed. That means we will hit the table caps on fields eventually. Not only that, but for each Service added we need to develop naming conventions around UI policies, client scripts, and custom fields to help manage this customization. We also now have a more complicated time pulling reports because we have hundreds of custom fields.

My question is why we "don't" advise extending HR tables to make the application more scalable. What is the reasoning behind siloing the work to just the OOTB tables and why was it built in this restrictive manner? I would much rather design my own data model than be forced into one that makes it hard to manage the more I use it.

[Suzanne H]

Jordan,

I can't answer your questions about why the tables were built the way they were and why you shouldn't extend them but I can speak about the max row space because we hit the limit last year. You have to be very critical of requests for new fields because they will reach the limit. Was your example of needing 100 services a hypothetical? I can't imagine why any company would need that many but especially that many on one table.

The maximum number of columns on the HR Core Case table is 453. Your client will eventually get an error message when trying to create new dictionary entries; Syntax Error or Access Rule Violation detected by database Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

This is what a Now Support engineer told us:

• The fields that take up the most space are varchar(). Varchar type 255 or lower takes up is three times higher than a varchar(256) or higher.
• Column widening where if a column does not need to be indexed then it could possibly be moved to varchar(256) or higher to offrow the field as a mediumtext field
• Use True/False, Yes/No, Choice or Reference fields instead of String fields whenever possible
• Move variables to extended tables (delete from HR Core Case table and recreate on other HR table)

[Jordan Mccann2]

Hello @Suzanne H ,

Firstly my example of 100 services was hypothetical to help show the scalability issue with the OOTB COE data model.

That is good to know. So, they advise you to extend the tables and migrate custom columns to a new table. Is there some challenge to migrating all the data over to the new table and removing the custom fields from your core table? I assume you would run into issues removing the columns with reporting and list and form views changing. User preferences would be reset, and relationships would need to be rebuilt using the new table.

We have a few of the COE tables reaching that limit, and this was my concern. 

One example from my customer is the "Investigations" COE table. They have multiple types of investigations that need to track important information for that investigation type. For example, a corporate investigation needs different information than an employee investigation. The thought was to extend the investigation table for each type as they require some workflows to function the way HR wishes. I could put them on the core investigations table, but I fear this will eventually become a scalability issue like you are seeing on your HR core case table.