Back to articles list
Various data modeling tools allow modelers to define relationships in a data-model as identifying or non-identifying. We can define a relationship as identifying or non-identifying in Vertabelo as well. This article will explain the way to do so.
Introduction
Before moving ahead with the article, I’d like to explain what identifying or non-identifying mean.
Let’s take a real time example of a book storing system. In the system, a book belongs to an owner, and an owner can own multiple books. But the book can also exist without the owner and it can change the owner. Thus the relationship between a book and an owner is a non-identifying relationship.
Now suppose one intends to keep a record of chapters included in a book. We know that chapters will only exist when a book exists. Thus the relationship between a book and its chapters is an identifying relationship.
In database terms, relationships between two entities may be classified as being either identifying or non-identifying. Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity’s primary key. In addition, non-identifying relationships may be further classified as being either mandatory or optional. A “mandatory” non-identifying relationship exists when the value in the child table cannot be null. On the other hand, an “optional” non-identifying relationship exists when the value in the child table can be null.
Identifying and Non-Identifying Relationships in Vertabelo
While working with Vertabelo, I realized that one good thing about the tool is that it keeps everything as simple as it appears in the real world. I eventually got to know from the blog post that it is one of the main reasons (reason# 03) amongst the top five reasons behind building Vertabelo.
In a physical model, Identifying and non-identifying relationships are built either (1) by mean of composite primary key OR (2) by imposing NOT NULL constraint OR (3) by applying 1st and 2nd points both.
Vertabelo allows modelers to build these relationships in a data model. Let’s see how it can be achieved.
The table below illustrates how and by what means one can establish these relationships:
| Relationship | Mandatory/ Optional | Composite Primary Key | Mandatory Check box |
|---|---|---|---|
| Identifying | - | Yes | Checked |
| Non-Identifying | Mandatory | No | Checked |
| Non-Identifying | Optional | No | Un-checked |
I have made Book_id as part of the primary key in the Chapter table. It defines an Identifying relationship between Book and Chapter tables.
To establish Non-Identifying relationship (mandatory) between Book and Owner tables, I added Book_id as a foreign key in the Owner table, and keep its Mandatory flag “Checked”. It means the system will never allow null values in the Book_id column in the Owner table.
In order to convert mandatory to optional, one just needs to uncheck the “Mandatory” flag on the column. As soon as you uncheck the mandatory checkbox, you would notice ‘N’ against the book_id column in the Owner table. In this case, Vertabelo automatically checks the “Null” checkbox against the column in the table definition so that it allows null values into the column.
Conclusion
Nowadays, data modelers follow a common practice to keep a surrogate key as the primary key in each table; therefore, the existence of a genuine Identifying relationship in a data model becomes rare. It is rarely seen that a foreign key is part of primary key in a child table.
