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 using database table keys.

As we briefly mentioned in a previous chapter, records have a primary key (PK), and a foreign key (FK) column; though they aren't called that in ServiceNow. The PK in ServiceNow is the Sys ID [sys_id] column. Every record in ServiceNow has a Sys ID that is typically unique within the entire database (though technically a primary key only needs to be unique within a table). 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. A reference field can only reference records in one table, specified in that field's dictionary record.

Note

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), but not the other way around.

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

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

Products

Note

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 primary key is usually long and randomly generated, like a Sys ID!

Note that in the preceding table, we have a primary key; that's mandatory. But we don't have any foreign keys. Foreign keys are not required in a table, just primary keys are!

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's two foreign keys! That table might look something like this:

Orders

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.

We don't have the shoe size in the Orders table, but by accessing the reference to the Customers field, we can get Larry Lopez's shoe size from his profile in the Customers table. This is called dot-walking.

Of course in a real-world scenario, 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.

Note

While you can have more than one foreign key column on a table, and you can even 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 called 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:

上一章目录下一章