As a solution architect implementing ServiceNow solutions, I've often struggled with data storage and planning where and how to store data occupies my mind more than anything else. Balancing security, query performance, reporting, and analysis, along with the type of data is a constant consideration.
In the world of ServiceNow, effective data storage design is key to creating solutions that are scalable, easy to maintain, and aligned with business requirements. The platform provides several options to store and manage data, each with its own strengths, limitations, and best-fit scenarios. Selecting the right method can simplify development, improve performance, and reduce future maintenance needs. In this post, we’ll dive into four primary data storage options in ServiceNow: custom tables, the Question Answer (Q&A) table, dynamic records with JSON fields, and extending base tables. By understanding each approach’s pros, cons, and ideal use cases, you’ll be better equipped to choose the right solution for your projects.
This post is only scratching the surface on the topics but the aim is to provide a high level considerations when implementing any solutions in ServiceNow.
Note: Please note that this post focuses solely on the development and implementation aspects and does not take subscription costs into consideration.
Creating a custom table with explicitly defined fields is one of the most common approaches for data storage in ServiceNow. This method involves designing a table with specific fields tailored to the requirements of a particular service or process. Custom tables provide a well-defined structure, making them suitable for data that has consistent and predictable attributes.
The primary advantage of custom tables is the structured data model they offer. With clearly defined fields and relationships, custom tables enable ServiceNow to optimize queries, which can enhance performance, especially for complex processes. Since each field has a set data type and constraints, it’s also easier to implement data validation rules, ensuring data accuracy and integrity. Additionally, custom tables allow for field-level access controls, making it straightforward to enforce data security and permissions.
However, custom tables come with certain trade-offs. They require more time to design and set up, as each field and relationship needs to be mapped out. This approach can also increase maintenance overhead, particularly if business requirements change over time, leading to schema modifications. To minimize these adjustments, consider designing tables with future scalability in mind and using modular designs that can adapt to new requirements. Custom tables may also be affected by ServiceNow upgrades, especially if they extend core tables.
Custom tables are ideal for scenarios where data consistency and structure are important. Examples include employee records, case management, and asset management, situations where having predefined fields and relationships facilitates reporting, data integrity, and security.
The Question Answer (Q&A) table approach is commonly used in ServiceNow’s Service Catalog and is designed for storing data in a flexible key-value format. Service Catalog items, for example, use this structure, where each entry in the Q&A table represents a “question” (or field) and an “answer” (or value). This approach is well-suited for capturing data with dynamic or varied structures. It’s particularly useful when there are multiple services with different data needs, as new fields can be added simply by defining new questions, with no schema modifications required.
This flexibility makes the Q&A table ideal for quickly configuring forms and capturing diverse data points. For instance, service requests with unique fields can be implemented rapidly, as each field can be defined as a question on the form. Since the table structure remains consistent, adding new questions doesn’t require schema changes, reducing maintenance and allowing for rapid adjustments.
However, the Q&A model has limitations, especially in performance and reporting. Storing data as key-value pairs can complicate querying and reporting, as each question and answer is stored independently, often requiring custom scripts to extract insights. Performance can also suffer when managing large amounts of Q&A data, as querying key-value pairs is less efficient than querying predefined fields. To avoid performance issues, consider limiting key-value pairs for high-traffic data, reserving this model for dynamic form configurations that don’t need frequent or complex reporting. Additionally, this approach has limited data validation capabilities, which can lead to inconsistencies if data isn’t carefully managed.
The Q&A table is a good fit for dynamic or variable data capture, such as service catalog forms, surveys, and ad hoc data collection. It’s also suitable for feedback forms and other situations where fields differ across services but don’t require strict data structure or validation.
Extending a base or out-of-the-box (OOTB) table allows you to build on ServiceNow’s existing tables, such as Task, cmdb_ci, sn_hr_core_case, and others, by creating a new table that inherits the fields, logic, and ACLs of the parent table. This approach is useful when your data requirements align closely with existing ServiceNow functionality but require some level of customization or specialization.
By extending a base or OOTB table, you gain access to built-in fields, workflows, and access controls, which can speed up development and provide consistency with ServiceNow’s standard practices. For example, extending the Task table provides access to fields like state, assignment, and SLA workflows, while inheriting core features and integrations. This approach also simplifies data integration with related modules, as extended tables are recognized by ServiceNow’s native functionalities.
However, extending base or OOTB tables can also introduce limitations. The inherited structure may not perfectly fit unique requirements, and customizing or removing inherited fields can be complex. This approach may also lead to data bloat if unused fields are inherited, impacting performance. Additionally, enforcing custom access controls on inherited fields can be challenging, especially if standard ACLs don’t meet specific security needs. Extending OOTB tables can also be affected by ServiceNow upgrades, requiring testing to ensure compatibility.
Extending base or OOTB tables works well when your requirements closely align with ServiceNow’s core modules but require some customization. Common use cases include custom case types or specialized asset tracking. This approach is suitable when you want to leverage existing ServiceNow functionality with minimal modifications.
Storing data within JSON fields provides a schema-less, flexible approach that’s particularly useful for applications with diverse or frequently changing data requirements. This approach involves saving data as JSON objects within a single or few fields in a general-purpose table, enabling you to store varied data structures without modifying the schema.
JSON fields offer extreme flexibility, as data can be added, modified, or removed within the JSON object without schema changes. This reduces schema management overhead and makes it easy to accommodate evolving data requirements. JSON is also widely supported for data exchange, simplifying integration with external systems.
The downsides are primarily related to querying and performance. ServiceNow’s native reporting tools aren’t optimized for JSON, so extracting data typically requires custom scripts. Performance may also degrade with large JSON objects, and JSON fields don’t inherently support data validation, necessitating custom scripts to enforce data quality.
JSON fields are ideal for cases where flexibility is important, such as customizable user settings, event logs, or product configuration data. They’re best used when data structures are expected to change frequently, and there’s no immediate need for structured reporting.
When maintaining a full record of data changes over time is essential, such as tracking updates to an HR profile or changes to position attributes due to personnel action requests, historical data tracking is invaluable. Rather than overwriting existing data, this approach captures both the old and new values for each change, preserving a full audit trail. This method is crucial in transactional contexts where record evolution must be documented for compliance, auditing, and reporting.
ServiceNow provides an out-of-the-box Audit table for this purpose, which can automatically track changes to specific fields across tables. This built-in feature records details such as the old value, new value, changed field, timestamp, and the user who made the change. For straightforward historical tracking needs, the Audit table can be highly effective, requiring minimal setup to start logging updates. However, security is a key consideration, especially if the data includes PII or PHI. Apply Access Control Lists (ACLs) to limit access to sensitive audit logs, and consider using Edge Encryption for added protection if your organization handles highly regulated data.
However, the Audit table has limitations that may make it unsuitable for more complex historical tracking requirements. Primarily, it is designed to track field-level changes and lacks the flexibility to handle complex tracking needs, such as maintaining data relationships or conditional rules based on specific changes. The Audit table’s structure can also present challenges for advanced reporting, as analyzing historical data across multiple related records may require custom reporting logic. Additionally, performance can be a concern when auditing tables with frequent updates or a large number of fields under audit, as this can quickly increase the Audit table’s size, necessitating ongoing maintenance to prevent performance degradation. Security is another concern when reliant on the OOTB audit table as PII and PHI might be exposed on these tables if they are not carefully excluded.
In cases where detailed historical tracking is necessary, creating custom tables can provide a more robust solution. Custom tables allow for the storage of each change as a separate record, preserving a full snapshot of data at each point in time. This approach is especially valuable when tracking transactional records that require multiple data points or relationships with other tables. Custom historical tables also support custom retention policies, which help manage table size and comply with data retention regulations like GDPR and HIPAA. For example, each update to an employee’s profile, such as changes to job title, department, or manager, can be stored as a new record, preserving previous data while capturing current values. This setup enables complex queries and in-depth reporting, allowing for analyses of trends, summaries, and relationships across records over time.
Here's an example table using the "end of time" approach, where each record has effective_begin_date and effective_end_date fields. The current (most recent) record has an effective_end_date set to 2299-12-31 to indicate it is the current active record.
Example: In this scenario, let’s say we’re tracking job title changes for an employee named "John Doe":
| Employee ID | Employee Name | Job Title | Effective Begin Date | Effective End Date |
|---|---|---|---|---|
| 1001 | John Doe | Junior Analyst | 2022-01-01 | 2022-12-31 |
| 1001 | John Doe | Analyst | 2023-01-01 | 2023-06-30 |
| 1001 | John Doe | Senior Analyst | 2023-07-01 | 2024-03-31 |
| 1001 | John Doe | Lead Analyst | 2024-04-01 | 2299-12-31 |
Explanation of Each Row
This structure allows you to query for historical data by specifying a date range or to retrieve only the current record by filtering for effective_end_date = 2299-12-31.
For solution architects in ServiceNow, understanding these data storage options and historical tracking methods is key to designing scalable, compliant, and efficient solutions. The choice of data storage, whether it’s custom tables, the Question Answer table, JSON fields, or extending OOTB tables, directly impacts performance, flexibility, security, and maintainability.
Incorporating historical tracking, whether through the OOTB Audit table or a custom historical table, ensures data integrity and compliance. Custom tables for complex tracking provide robust reporting and secure access, helping reduce the risk of data exposure.
Ultimately, a well-planned data architecture that considers both current and historical needs enables architects to build high-performing, adaptable applications that support long-term scalability and security.