This topic discusses the factors to consider when determining whether to store different types of records within a single table or a subtable. The decision of whether to use a subtable is based on user requirements and the extent of the differences between record types. These guidelines can help you decide whether a single table or a subtable is the most efficient implementation option for your requirements, then consider the implications of each design choice, as well as the best practices and caveats for using subtables if you decide that a subtable is the best design decision.
There are often situations in which you may have a table of records, and yet some of the records are substantially different in terms of record type, fields needed, layouts, default values, permissions, and rules.
A subtable is a subset of a main table that is defined by the Type field, which links the records to the tables in the database. The Type field is part of the parent table in the database and indicates which table a record is on, which enables records to be reassigned to a different subtable.
Subtables can share fields with their sibling tables (and parent tables) but maintain separate permissions, views, layouts, and other functions.
When first created, the subtable will inherit all of the settings from the parent table. These include everything in the parent table wizard, such as fields, layout, rules, permissions and others. These can be used or not, as required for the purposes of the subtable.
Once created, the subtable's setup wizard can be accessed from the left pane.
Changes made in the setup wizard will only apply to the selected subtable. This provides flexibility and independence between the different subtables. For example, subtable-specific fields can be created within the subtable's setup wizard, and will only be seen within that subtable. The set of fields that are common to all subtables can be added to the top-level table, and these fields can then be used in any subtable.
Subtables have many advantages in terms of providing separate functionality for different types of records in the same table. However, they can result in a more complex system that is difficult to maintain.
The alternative to a subtable is to hold the record type within a custom field. This field can then be used to determine the visibility of other fields in the record or be used in filters to determine which rules should run. Single tables are often less complex to set up and maintain, but do not easily handle substantial differences between types of records.
A second alternative is to create separate top-level tables to hold the different record types. This is best when the automation, permissions, and so forth are so different that a single table would not be manageable but the added complexity of subtables is disadvantageous.
This article continues with a comparison of using a subtables vs. a record type field in a single table.
Think about the following questions to help determine if a subtable is the better design choice.
Below are a few examples in which a decision had to be made either to use subtables or keep all records in a single table.
The following examples resulted in a decision to use a subtable rather than a single table.
The People table has two subtables: Employees and External Users. The following factors were considered when determining whether subtables should be used:
Field Diversity: The fields needed for each subtable vary widely. For example:
This is a wide range of fields with specific uses for only one or two of the three possible subtables. Subtables better handle such complexity in field availability for different records. In addition, layouts can be created separately for each subtable, ensuring that each type of record only shows the fields that are relevant. A view of each subtable is shown below to demonstrate the variety of fields and tabs needed for each subtable.
Field Overlap: Although there is a distinct set of fields needed for records in each subtable, each subtable also shares many fields with other subtables, and many fields are common to all subtables as well as the parent table. For example, the fields First Name, Last Name, Company, Direct Phone, and Email are applicable to all those in the People table, the Employee subtable, and the External User subtable. There are also fields that are common for two of the three subtables, such as Account Rep, which is used for External Users and People.
Rules: Although the People table is a background table and therefore has limited automation, there are a couple of rules that are specific to a certain subtable. The External User subtable requires a rule upon creation to set the value of its Groups field. The Employee table requires a rule running when a flag field changes in order to create a document approval with that employee as the Approver. This functionality is not required for any of the other subtables, as only employees will be approvers of documents.
Links from Other Tables: There are many links to the People table within other tables. Often, these links to the People table do need to be filtered to only the people in a specific subtable. In this case, having subtables is helpful as the link can be directed to the subtable itself instead of having to apply a filter. However, another factor to consider is that some links to the People table are meant to hold more than one subtable. For example, if a task is created for a person, the person can be anyone in any of the subtables, and is often either an External User or a Person. This requirement reminds us that despite the differences between the People subtables, they still belong in the same table.
The People table holds records for those who do not need to log into the system, but whose information must be contained within . This is the top-level parent table of the other two subtables. There are several sales-related fields that are needed for people in the Sales Progress tab.
The Employee subtable is meant for internal users of the system. They each need a Login, Password, Primary Team, Teams, and Groups. There are many fields that only apply to Employees in the Job tab, including the employee's Manager Name, Hire Date, Home Street Address, and Backup Person.
Along with the Employee subtable, the External User subtable also requires these fields:
There is information specific to the account of the external user in the Account Info tab, which is similar to the Sales Progress tab for a person, but which has more information about the location of the external user.
The Companies table holds information about companies. In many cases, different types of companies can be categorized by a Role or Type field. However, sometimes the types of companies are distinct enough to warrant subtables.
In one of 's internal KBs, the Companies table contains three subtables in addition to the top-level Company table: Accounts, Partners, and Vendors. Screenshots of records belonging to each of the three subtables are shown below. The field and layout differences among the three screenshots are indicative of the distinct ways that the three subtables are used.
The Accounts subtable holds records for customers. These records are created regularly by conversion actions from the Leads table, for instance, when a potential customer selects the company as a service provider. This table is a great access point for related records, and KB, Contract, Order, Project, License and Ticket records are all linked to the main account record for each company.
The Partners subtable holds records for reseller partners. Partner records hold partner questionnaire responses and information about the current partnership, as well as links to some of the same related data as account records.
The Vendors subtable holds records for vendors from which goods and services are purchased. Vendor records capture login/password data and other information to help employees access vendor-related accounts and resources.
In knowledgebases set up for asset polling, subtables are used in the Asset Details table:
These subtables must belong to the same top-level table because they are all types of assets, and all must link to the Assets table. In addition, it is useful to allow reporting on all of the Asset Details records together. However, each subtable requires a unique set of fields, which led to the decision to create subtables. For instance, the Disk Drive subtable contains the fields Disk Name, Disk Size, and Free Space only, the Network Share subtable uses the fields Path and Share Name, and the VLAN subtable contains the fields IP Address, Port Name, and VLAN Name. Completely separate layouts also make it simpler to separately arrange each unique set of fields for each subtable.
In many cases, a single table without subtables is a simpler solution. However, a single table lacks some of the ability to set up separate functionality for records of distinct types. Below are some cases where the decision was made to create a single table, and the visibility-dependent fields that were created to support this.
In contrast to the above example of the Companies table, in which subtables were the best option for storing companies of very distinct types, it is often preferable to keep companies in the same table. Our default contract management knowledgebase stores all types of companies within the same table:
Instead of assigning companies to different subtables for Customers, Partners, Prospects, etc., the fields Primary Role and Company Roles are used to distinguish different types of companies:
There were several factors considered before making the decision to use a single table.
Fields can be visibility dependent on the Primary Role or Company Roles fields. For example, the fields Industry and Region are only visible when the Primary Role is Customer, and the fields Use as Reference and Reference as of are only visible if the Primary Role is Customer or Partner.
As you can see, there are not many fields that are visible based on the company's role, and so they can be handled with the visibility-dependence field setting.
In another system, subtables were considered as a way to hold the nine main categories or roles of companies but ultimately decided against. Some of the factors were:
The last requirement – that one organization can have multiple roles – can be handled only by a single table with a multi-choice field. If subtables are used for the different organization roles, each organization would only be able to have one role.
The other requirements can be handled with either solution. Subtables would better handle field visibility and the ability to view all fields for each role separately. Different layouts to hold these different sets of fields are only possible with subtables, but a similar and satisfactory result can be achieved by setting each field's visibility options:
Subtables have built-in separate permission settings, so that each subtable can have completely independent permissions. However, some permission settings can be handled by a single table and a "roles" field. In this case, restricting the view access of other peoples' organizations with a certain role can be managed within a single table by using a permission filter:
Links to subtables are automatically added below the main table in the left pane, but this can also be done manually by creating saved searches based on the role field and then selecting the Show in Left Pane checkbox.
In this case, the requirement that organizations have multiple roles, along with the factor that all of the other requirements could be handled with some pros and cons on either side by both solutions, led to the decision not to use subtables.