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?

[Pradeep Sharma]

Hi Anthony,

You may find below thread helpful.

Maximum number of fields in a table

Modifying the Application Design - ServiceNow Wiki

[Chuck Tomasi]

Per the MySQL docs, there is a hard limit of 4096 columns per table.

MySQL :: MySQL 5.7 Reference Manual :: C.10.4 Limits on Table Column Count and Row Size

[M Iftikhar]

Hi guys, I had a similar question. I am developing an application for 'Procurement' with main one custom table I got around 6 forms with each form on average there are 30-50 fields . Now either I create a custom table for each form or move all the fields in one big custom table. 1) New table for each form will make the ERD looking good but apart from this don't see any other advantage. 2) Moving all fields to one custom table will make the things simple. 3) Moving all fields in one table will make the saving a form/manipulating in scripts make it easy so easy for development? it will also save development time as I don't have to create multiple table. Please suggest based on your experience the best practices to create a new table or adding fields to table. I don't think I will exceed the limit of number of columns in a table but wanted to make sure I am not creating mess either. by the way there is already an application created in my company which is with one large big table with around 300 fields. They only created new tables where relationship was one to many otherwise for all one-to-one relationship they added new fields. Please suggest?

[Chuck Tomasi]

4000 fields is a lot. I don't completely understand why you think you need one table or multiple tables without understanding more about you process. Nor do I understand if "multiple forms" is separate records or the same record viewed multiple ways. What I can tell you from experience is that nobody likes to fill out long forms. 🙂 After about 30 or 40 fields, people just give up and you start getting meaningless or missing data.

If you have multiple forms that do multiple functionalities, consider separate tables, either as standalone tables, or as table extensions. Look for commonality between the forms/processes.

Consider the use case of task as the base table and incident, problem, change_request, sc_task, sc_request and many others extended from it. Task as a lot of common fields, but incident really doesn't have that many for itself. This keeps things organized. (Under the hood there is quite a bit of other stuff going on that I wont get in to here.) You can create a table that extends task and get all those fields for "free" then start building your process specific fields on top of that. Assigned to, Assignment group, state, etc. are "built in" and accelerate your app dev.

You have the ability to create extensible tables (from the table form) when you create the table. Even if your form has no task related type requirements, create your base table, then add specific fields to that. As a simple case, I was recently building an app and noticed that I had the same 'text' and 'order' fields on a couple tables. Rather than make them two different tables, I made a hierarchy to demonstrate something.

Base table: Question (extensible)

• Field: Text (string)
• Field: Order (integer)

Table: Review question (extends Question)

• Field: Review task (reference to Review task table)
• Field: Score (Integer)
• Field: Comments (string)

Table: Question score (extends (Question)

• Field: Review (reference to Review table)
• Field: Score (Decimal)

Now Review question contains fields Text, Order, Score, Review, Comments. It gets Text and order by inheriting, allowing me easier maintenance to the tables if I need another 'shared' field between them, I put it on Question. If I need a field specific to either Review question or Question score, it goes on those tables.

For the sake of maintenance, I urge you to consider alternatives to making one monolithic table if you are using multiple processes/forms.