Any issues with large number of fields on tables

[anthony_symons]

Hi All

We have an on premise ServiceNow. On the task table there are currently around 500 fields. These are required for the complex business processes, and the use of a large number of task applications.

Has anyone seen issues with creating a large number of fields? I believe that MySQL has a limit of around 1000 fields.

Also is there any best practice in how to create related tables so that we don't need to have all fields in the one table?

[M Iftikhar]

Thanks for your detailed answer.

The high level process is there are total 9 different routes to the 'Procurement' process. Each route has distinct multiple gateways/STEPS. On each gateway they fill a form.

'Decision gateways' or simply STEPS in ONE route and on each gateway they fill a form. After all the stages completed in any route it result 'Procurement' process mark as completed. Beside filling each form on each gateway/STEP there are certain people who also approve each gateway/STEP in procurement.

1) First I don't have 4000 fields in one table. If I do the maths above there will be total 180-300 fiels in total in a table.

2) Regarding adding those 300 fields is not about liking or disliking. I am working on 'Procurement & contracts' project and need to automate those forms and move them in SNOW. So its not me deciding adding/selecting forms fields to capture. I have to capture them all as per requirement (fields on the forms) .

3) There is no commonality and they are pretty much all distinct forms. If I will be honest then there are 1-2 attributes common in 2-3 forms and that's it. So I would not consider anything here for common though I really like to build something below hence asking a question.

Decision Gateway - DG

u_procurement_contracts extends task
u_procurement_dg1 extends u_procurement_contracts
u_procurement_dg2 extends u_procurement_contracts
u_procurement_dg3 extends u_procurement_contracts
u_procurement_dg4 extends u_procurement_contracts

and so on

For the purpose above I have created one 'Procurement' custom table which is already extended with 'Task' table as you mentioned. I have also used the 'Conditional process formatter' to show the different routes based on the category selected conditon.

so for Category A route might be DG1,DG2,DG3 process formatter
so for Category B route might be DG1,DG3 process formatter
so for Category C route might be DG1,DG2,DG3,DG4,DG5 process formatter

As each category has different route hence different people will be approving it. There are total 9 routes and 5 distinct routes. People on each gateway approving will be same so DG1 in Category A,B & Category C are the same people because the forms are same.

a) I can create one workflow for each category/route selected
b) Or create one large workflow for all routes and conditional select the routes in the workflow.

What would you do and why for workflow as well please?

If I would be developing a web app myself I will never create one table and store all forms fields in one table but the reason I ask here is it might be performance thing with SNOW and also make things complicated unnecessaryly.

Thanks again.

[M Iftikhar]

Last but not least. As I said the relationship of these attributes on the form with main entity is one to one. so for simple for procurement we have to capture 300 attributes whereas as per my knowledge you only create table when you have multiple records to the main entity. a) if main entity procurement got 300 attributes. the link here is one to one. b) if main entity procurement got multiple e.g let say stages then for stages I should create a table because the link here is one to many. another example is if a user got multiple addresses (number is unknown) then two tables one for user and other for addresses. whereas if user got 300 attributes name,age,id and so on then why would you create multiple tables to store one user entity and the link between these attributes and user is one to one. It might looks good in ERD form but over complicated and in my opinion difficult to maintain and extend? Please suggest if I am still incorrect?

[M Iftikhar]

It was very bad idea to create separate table for each form. If you are use case similar to below then I would high recommend to not create separate table for each form. 

Scenario:

If you are implementing a process which require filling 7 word documents which considered as 7 forms by the business. Now you need to capture the same data in ServiceNow and build some process around these 7 forms. Also you have to expose all these 7 forms in one screen means when you show form1 you have to show the data from form2-7 as tabs on the same screen. 

I would suggest to capture all the fields in ONE table as the relationship between the process and all the attributes on all 7 forms are one-to-one. But if you decide to 7 tables and try to show all tables2-7 data with table1 on the same screen. You will only face issues and there will be no advantage to creating separate tables. 

It was bad decision that I created 7 tables for the same process just because to normalise the data and they were in 7 word documents forms. 

Thanks

ifti