# Tables and Relationships

> Chapter 4 of the Courseiva SERVICENOW-CAD curriculum — https://courseiva.com/learn/servicenow-cad/tables-and-relationships

**Official objective:** 2.2 — Design and create tables, fields, and relationships

## Introduction

Tables and Relationships. This is the backbone of every application you will build in ServiceNow. Without understanding how to design tables and connect them, your data will be duplicated, messy, and impossible to report on – which is exactly what the SNOW-CAD exam wants to catch you out on.

## The University Course Registration Analogy

3 core things happen every semester: 1500 new students enrol, 400 courses are offered, and 12,000 individual registrations are processed. 

Imagine you are the university registrar. You need to know which student is in which course, their grades, and which professor teaches what. You could scribble it all on a whiteboard, but that would be chaos. Instead, you use three separate filing cabinets. 

Cabinet A is labelled 'Students'. Each drawer (a table) holds a folder per student with their unique student ID, name, and date of birth. Cabinet B is labelled 'Courses'. Each folder has a course code, title, and the professor's name. Cabinet C is labelled 'Registrations'. This third cabinet does not repeat all the student or course details. Instead, each folder in Cabinet C only contains two pieces of paper: a Student ID number that matches Cabinet A, and a Course Code that matches Cabinet B. This is the relationship. 

Now, to find out who is in 'Biology 101', you do not search through 1500 student folders. You simply go to the 'Registrations' cabinet, pull every folder with the Biology 101 course code, and then use those Student IDs to look up the full names in the 'Students' cabinet. This avoids repetition and keeps data clean. In ServiceNow, tables are the cabinets, and a 'reference field' is the link between them – just like the Student ID paper that connects a registration to a student.

## Core explanation

In ServiceNow, every piece of information lives in a table. A table is just a structured list of things. Think of a spreadsheet: the columns are fields (like 'Name', 'Email', 'Department'), and each row is one individual record (like one specific user). ServiceNow calls a row a 'record'. 

Every table in ServiceNow has a unique name, usually prefixed to avoid confusion. For example, the table that holds user accounts is called 'sys_user'. The table for incidents (reports of something broken) is called 'incident'. These are called 'base tables' because they are the foundation. 

When you create your own application on the ServiceNow platform, you will create custom tables. You can create an 'extended' table, which inherits all the fields from its parent table. For example, if you create a table called 'u_laptop' that extends the 'cmdb_ci' table (which stores configuration items like computers and servers), your 'u_laptop' table automatically gets fields like 'Serial Number', 'Manufacturer', and 'Model' without you having to type them in. This is called 'inheritance' and it saves time and keeps data consistent. 

Now, the magic happens when you connect tables. This is where 'relationships' come in. The most common type of relationship in ServiceNow is a 'reference field'. A reference field on one table points to a specific record in another table. 

For instance, every 'incident' record has a field called 'Caller', which references the 'sys_user' table. When you select a user as the Caller, you are not typing their name into the incident; you are creating a link (a reference) to that user's record. This means the user's phone number, email, and department are stored in one place (the 'sys_user' table) and are automatically available on every incident they are linked to. If the user changes their phone number, you only update the 'sys_user' table once, and every incident automatically reflects the new number. This is called 'data normalisation' – storing data in one place to avoid redundancy. 

There are also 'database views' which let you combine fields from multiple tables into a single virtual table for reporting, but the foundational relationship type you must master for SNOW-CAD is the reference field. 

Why does this matter for a developer? When you build an application, you need to decide how your tables relate to each other. The three main relationship types are: 

- One-to-many: One record in Table A can be linked to many records in Table B. For example, one 'Department' can have many 'Employees'. The reference field goes on the 'many' side (the Employee table has a reference to the Department table). 
- Many-to-many: Records in Table A can link to many records in Table B and vice versa. For example, a 'Student' can be in many 'Courses', and a 'Course' can have many 'Students'. ServiceNow handles this with a third 'intermediate' table (often called a many-to-many table or M2M table) that holds two reference fields – one to each of the original tables. 
- One-to-one: This is rare. One record in Table A is linked to exactly one record in Table B. An example might be a 'User' and their 'Emergency Contact' – each user has exactly one emergency contact record. 

The platform enforces 'referential integrity'. This means you cannot delete a record from a table if other records still reference it. If you try to delete a Department that still has Employees linked to it, ServiceNow will block the deletion or warn you. This protects your data from becoming orphaned – records that point to nothing. 

Finally, every table has a 'Sys ID'. This is a globally unique identifier (a 32-character string) assigned to every record automatically. You will never type a Sys ID yourself; the platform generates and uses it internally to maintain relationships. When a reference field is created, it actually stores the Sys ID of the target record, not the name or any other human-readable value. This is why records can be renamed without breaking links.

## Real-world context

Imagine you work for a medium-sized company called 'GreenLeaf Energy' that sells solar panels. The company has 200 salespeople, 1500 customers, and 8000 installation jobs per year. Your manager asks you to build a small ServiceNow app to track which salesperson is assigned to which customer, and which installation jobs have been completed. 

Here is what an IT professional actually does step by step: 

First, they analyse the business needs. They identify three core 'nouns' in the business: Salespeople, Customers, and Installation Jobs. Each of these becomes a table. 

Second, they create the tables in ServiceNow Studio: 
- 'u_salesperson' (extends the standard 'sys_user' table so it already has name, email, phone) 
- 'u_customer' (a custom table with fields like Company Name, Address, Phone) 
- 'u_installation_job' (a custom table with fields like Job Date, Status, Notes) 

Third, they define the relationships. A Salesperson can have many Customers (one-to-many). So the developer adds a reference field on the 'u_customer' table called 'Assigned Salesperson' that points to the 'u_salesperson' table. Now, when a customer record is opened, the user can pick the salesperson from a dropdown list – that dropdown is populated by the 'u_salesperson' table. 

Fourth, they build the relationship between Customers and Installation Jobs. One Customer can have many Installation Jobs (if they buy multiple systems over time). So another reference field is added to 'u_installation_job' called 'Customer', which points to 'u_customer'. 

Fifth, they create a 'Form' that brings all this together. When a user opens an Installation Job form, the 'Customer' field shows a magnifying glass icon. Clicking it opens a popup window showing the customer list. Once selected, the customer's address and phone automatically populate read-only fields on the form (using a business rule or a calculated field). This is possible because the relationship exists. 

Finally, they test. They run a report: 'Show me all Installation Jobs where the Salesperson is Bob Smith and the Status is not Completed'. The report engine uses the relationships to join the three tables and returns the correct data. Without properly defined tables and relationships, this report would be impossible or would return wrong results. 

The key actions a developer performs daily: 
- Creating new tables and deciding whether to extend an existing table or start from scratch 
- Adding reference fields to link tables 
- Building 'reference qualifiers' – filters that limit which records appear in the reference dropdown (e.g., only show customers that belong to the current salesperson's region) 
- Testing referential integrity – making sure deletion rules are set correctly (cascade delete, restrict, or set null) 
- Writing ACLs (Access Control Lists) that restrict who can see records based on the relationships (e.g., a salesperson can only see their own customers)

## Exam focus

The SNOW-CAD exam tests Tables and Relationships heavily – expect at least 3-5 questions on this topic. The exam does not ask you to memorise table names; it tests your understanding of concepts. 

Here is exactly what they test: 

- The difference between a 'base table' and an 'extended table'. A common trap question: 'Which table inherits fields?' The answer is the extended table. They might describe a scenario and ask whether you should extend an existing table or create a new one from scratch. The correct answer is almost always 'extend' if the new table represents a subtype of an existing entity (e.g., 'Laptop' extends 'Computer'). 

- Reference fields. They will show you a diagram of two tables and ask what type of field connects them. The answer is 'Reference field'. They love to ask: 'Which type of relationship is created by a reference field?' The answer is 'One-to-many' (from the table that has the reference to the table it points at). 

- Many-to-many relationships. They will ask how to implement a many-to-many relationship. The correct answer is: create a third table with two reference fields – one to each of the original tables. They might call this an 'M2M table' or 'intermediate table'. 

- Sys ID. A trick question: 'Which field uniquely identifies a record in a table?' The answer is 'Sys ID' (or 'Sys ID field'). They might ask 'Can two records have the same Sys ID?' No – it is globally unique. 

- Referential integrity. A question might describe a situation where deleting a record causes errors. The question: 'What concept prevents you from deleting a record that is referenced by other records?' Answer: 'Referential integrity'. 

- Database views vs. reference fields. A trap: they ask 'What is the best way to combine data from two tables for a report?' The answer could be either a 'database view' or 'reference field' depending on the context. If they ask about linking records for everyday forms, use reference fields. If they ask about a read-only report that does not need to update, use a database view. 

Exam traps to watch for: 
- Confusing 'reference field' with 'glide list field' (which stores multiple references, used for many-to-many). 
- Believing that an 'extended table' creates a copy of the data – it does not; it creates a logical link. 
- Thinking that a 'one-to-many' relationship requires a field on both tables – it only needs a field on the 'many' side. 
- Forgetting that the system automatically creates the 'Sys ID' field – you never add it manually. 

Key definitions to memorise: 
- 'Record': a single row in a table 
- 'Field': a column in a table (also called an 'attribute') 
- 'Reference field': a field that stores the Sys ID of another record and creates a link 
- 'Glide record': the JavaScript object ServiceNow uses to interact with a single record 
- 'GlideAggregate': used for querying and aggregating data across related tables
- 'Extended table': a table that inherits fields and configuration from a parent table

## Step by step

1. **Identify the core entities** — List the 'nouns' in your business requirement – for example, Customer, Order, Product. Each distinct noun will usually become its own table. This step ensures you do not cram unrelated data into one table.
2. **Create the tables in ServiceNow Studio** — Open Studio, create a new application, then create each table. For each table, decide if it should extend an existing table (like sys_user) or be completely custom. Choose the correct table name prefix (usually 'u_' for custom tables).
3. **Add fields to each table** — For each table, add the necessary columns such as Name, Date, Status. ServiceNow automatically adds the Sys ID field and a 'Created by' field. You add everything else. Use appropriate field types: String for text, Date for dates, Choice for dropdown options.
4. **Create reference fields to link tables** — On the 'many' side of a one-to-many relationship, add a Reference field that points to the 'one' side table. For example, on the Order table, add a reference field called 'Customer' that points to the Customer table. This creates the link.
5. **Configure reference qualifiers and delete rules** — Set a 'reference qualifier' to filter which records appear in the dropdown (e.g., only active customers). Also set the 'delete rule' for the reference field to Restrict, Cascade, or Set Null to control what happens when a linked record is deleted.
6. **Test the relationships by creating records and reports** — Create a few test records in the parent and child tables. Verify that the reference field populates correctly and that you can run reports that use the relationship. Check that deletion is blocked or handled as expected to confirm referential integrity works.

## Comparisons

### Base Table vs Extended Table

**Base Table:**
- Created as a standalone table without a parent
- Has its own set of fields and configurations
- Does not inherit anything from any other table

**Extended Table:**
- Created by specifying a parent table to extend
- Inherits all fields and behaviours from the parent table
- Can add its own additional fields on top of inherited ones

### Reference Field vs Glide List Field

**Reference Field:**
- Stores a single Sys ID pointing to one record
- Creates a one-to-many relationship
- Appears as a single-value dropdown on a form

**Glide List Field:**
- Stores multiple Sys IDs pointing to many records
- Creates a many-to-many relationship when used on multiple tables
- Appears as a multi-select list on a form

### One-to-Many Relationship vs Many-to-Many Relationship

**One-to-Many Relationship:**
- Implemented with a single reference field on the 'many' side
- One record in Table A can link to many records in Table B
- The 'one' side table does not need an extra field

**Many-to-Many Relationship:**
- Requires a third intermediate table with two reference fields
- Many records in Table A can link to many records in Table B
- Both tables need a path through the intermediate table to see linked records

### Database View vs Reference Field Link

**Database View:**
- A virtual table that combines fields from multiple tables
- Read-only – you cannot update data through a view
- Used primarily for reporting and read-only dashboards

**Reference Field Link:**
- A direct link between two actual tables
- Allows updating data through forms on either side of the link
- Used for day-to-day transactional data entry and record management

## Diagram

_This flowchart shows how different tables in ServiceNow are linked through reference fields, including one-to-many (Employee to Department) and many-to-many (M2M Table connecting Customer and Product) relationships._

```mermaid
flowchart TD
    A[Customer Table] --> B[Order Table]
    B --> C[Product Table]
    D[Employee Table] --> A
    D --> E[Department Table]
    F[M2M Table] --> A
    F --> C
    E --> G[Company Table]
    H[Incident Table] --> D
```

## Common misconceptions

- **Misconception:** I can just put all data in one table and use filters to separate it. **Reality:** Data should be split into separate tables based on the entity (e.g., one table for Customers, one for Orders). This avoids data duplication and makes updates easier. A single table for everything leads to massive redundancy and slow performance. (Beginners think a spreadsheet where they can hide columns is the same as a database. They do not yet understand that databases enforce structure to prevent errors, and that repeating data (like a customer's address on every order) leads to inconsistencies when the address changes.)
- **Misconception:** A reference field stores the entire record of the related table. **Reality:** A reference field stores only the Sys ID (a short text string) of the related record. The platform uses that Sys ID to look up the full record when needed. This keeps the database efficient and avoids copying data. (The form shows the name of the related record (e.g., the caller's name), so beginners assume the whole name is stored in the field. They do not realise the field contains a hidden identifier that is translated into a human-readable name on the form.)
- **Misconception:** Extending a table means the child table gets a copy of all the parent table's data. **Reality:** Extending a table means the child table inherits the structure (fields, forms, policies) but does NOT copy the data. When you query the child table, you see only its own records plus the inherited fields. The parent table's records are not duplicated. (The word 'extends' sounds like it creates a physical copy or clone. Beginners visualise it as duplicating the entire parent table. In reality, it is a logical parent-child relationship in the database schema.)
- **Misconception:** You need a separate reference field on both tables to create a one-to-many relationship. **Reality:** A one-to-many relationship requires a reference field only on the 'many' side table. The 'one' side table does not need a field pointing back. For example, an Employee table does not need a field pointing to the Department table – the Department table is referenced by the Employee table. (Beginners think relationships must be symmetrical (like a two-way street). They do not realise that you can query the 'many' side to find all records linked to a 'one' side without storing anything on the 'one' side. The database can do reverse lookups using the reference field on the other table.)
- **Misconception:** If I delete a record from the parent table, it automatically deletes all child records. **Reality:** This is not automatic. The behaviour depends on the 'delete rule' set on the reference field. It can be set to 'restrict' (block deletion), 'cascade' (delete children), or 'set null' (remove the reference). By default, ServiceNow prevents deletion (restrict) to protect data integrity. (Beginners assume inheritance implies automatic deletion, like deleting a folder in a file system deletes its contents. In a database, this is a configurable choice because the consequences of accidental mass deletion are severe.)

## Key takeaways

- A table in ServiceNow is a structured list of records, where each column is a field and each row is a single record.
- A reference field is the most common way to link two tables together, creating a one-to-many relationship.
- Extending a table allows your new table to inherit all fields, forms, and behaviours from the parent table without duplicating them.
- Every record in ServiceNow has a globally unique Sys ID that is used internally to manage relationships between tables.
- A many-to-many relationship requires a third intermediate table with two reference fields – one to each of the original tables.
- Referential integrity prevents you from deleting a record that is still referenced by other records in the system.

## FAQ

**What is a table in ServiceNow?**

A table is a structured collection of records, similar to a spreadsheet. Each column is a field (like Name or Email) and each row is one individual record (like one specific user or one incident).

**How do I link two tables together in ServiceNow?**

You add a Reference field to one of the tables that points to the other table. For example, to link an Incident to a User, you add a Reference field called 'Caller' on the Incident table that references the sys_user table.

**What is the difference between a base table and an extended table?**

A base table is a standalone table with its own fields and configuration. An extended table inherits all the fields, forms, and behaviours from a parent table, allowing you to add extra fields without starting from scratch.

**Can I delete a record that is linked to other records?**

By default, ServiceNow prevents you from deleting a record that is referenced by other records. This is called referential integrity. You can change the delete rule on the reference field to allow deletion with cascading behaviour if needed.

**What is a Sys ID and why is it important?**

Sys ID is a globally unique 32-character identifier assigned automatically to every record in ServiceNow. It is used internally to link records across tables via reference fields, ensuring that relationships remain stable even if human-readable names change.

**How do I create a many-to-many relationship in ServiceNow?**

You create a third table (often called an M2M table) that contains two reference fields – one pointing to each of the original tables. Each record in this third table represents a link between one record from the first table and one record from the second.

## Check your understanding

1. **What type of relationship does a Reference field create between two tables?**

   Answer: A Reference field creates a one-to-many relationship, where the table containing the Reference field is the 'many' side and the table it points to is the 'one' side.

2. **How do you implement a many-to-many relationship in ServiceNow?**

   Answer: Create a third intermediate table (M2M table) that contains two Reference fields – one to each of the original tables.

3. **What is the difference between a base table and an extended table?**

   Answer: A base table is standalone with its own fields; an extended table inherits fields and behaviour from a parent table.

4. **What happens by default when you try to delete a record that is referenced by other records?**

   Answer: By default, ServiceNow blocks the deletion (referential integrity) to prevent orphaned records.

5. **Which field is automatically created for every record in ServiceNow and is used to uniquely identify it?**

   Answer: The Sys ID field – a globally unique 32-character string assigned to every record.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/servicenow-cad/tables-and-relationships