One-to-many relationships in ServiceNow

One-to-many relationships are one type of parent-child relationship. They consist of one parent record, that is linked to many child records. This linkage is done via database table keys.

As we briefly mentioned in a previous chapter, records have a primary key (PK), and a foreign key (FK) column. The PK in ServiceNow is the Sys ID. Every record in ServiceNow has a Sys ID that is unique within the entire database. An example of an FK column, is any column which is meant to hold the PK of another record. These fields are Reference fields in ServiceNow. The Incident [incident] table for example, contains an FK column with the label Assigned to, and an actual column name of assigned_to. This is a Reference field that points to the User [sys_user] table, and contains the PK (Sys ID) of one of the records in that table.

When you look at one of these FK/Reference fields on a form, you'll see the display value of the record that the Sys ID it contains corresponds to. The display value is the value in whichever column has the display_value attribute set to true. For example, in the Incident table (and any other tables that extend the Task table), the display value is the Number column by default.

The Assigned to field also helps to illustrate the nature of a one-to-many relationship. As you might imagine, each incident can only be assigned to one user at a time (you can only put one Sys ID value into a Reference field at one time!) However, a user may be the assignee of many incidents. Therefore, the relationship is one (user) to many (incidents).

To get a better idea of how this relationship works, let's explore the database of an imaginary shoe-shop: Lou's Shoes.

Imagine that we have a table of customers that looks something like this:

Customers

Name (PK)	Shoe size	Preferred Material
Larry Lopez	11 Men's	Leather
Sally Stewart	7 Women's	Suede
Molly Mae	8.5 Women's	Microfiber

Next, imagine that we've got a table of products, like this:

Products

Item (PK)	Color	Material
Lavender Leather Loafer	Purple	Leather
Scarlet Suede Stiletto	Red	Suede
Mahogany Microfiber Moccasin	Brown	Microfiber

Of course, it's a bad idea to use a customer or item name as a PK like we have in the preceding two tables, because sometimes people or items have the same name! This is just for the purposes of our example. The best PK is usually long and randomly generated, like a Sys ID!

Finally, we've got to have a place to store orders; but orders must contain links to both an item, and to the user who ordered them! That table might look something like this:

Orders

Order # (PK)	Customer (FK)	Item (FK)
018239	Larry Lopez	Lavender Leather Loafer
018240	Sally Stewart	Scarlet Suede Stiletto
018241	Molly Mae	Mahogany Microfiber Moccasin

As you can see, the Orders table consists of only keys-one primary (as always) and two foreign. The primary key is the order number. This is not randomly-generated, but assuming it's auto-generated and guaranteed unique, it can work for us. The other two fields/columns are both foreign keys. One links to the customers table, and one to the items table we defined earlier. Each record in the Orders table, therefore, is an order placed by a customer, for an item. For example, Larry Lopez placed an order for size eleven Lavender Leather Loafers at Lou's Shoes.

I hope he liked them.

Of course in real life, you could have more than one item in an order and you'd definitely want more information stored with the order (such as the price and where to ship it!) but this is just an example.

While you can have more than one foreign key column on a table, and you can surely have more than one column that should contain unique values, only one column in a given table can be the primary key.

When we added the Major incident field to the Virtual  War Room table, we created a one-to-many relationship between the Incident and Virtual War Room tables, with the incident being the parent, and the virtual war room being the child. We then displayed that relationship on the incident form, by showing the related list Virtual War Rooms | Major Incident (as long as it isn't empty). This is often how one-to-many relationships work, and are displayed within ServiceNow: