What is a Parent and Child Data Relationship?
Welcome back to our database basics series where we are breaking down the complex architecture of modern software databases so that you can better understand how to leverage them in your business. This post is focused on relationships between fields and data, specifically parent-child relationships.
Lookup Field Relationships
In the previous post, we discussed the one-to-many lookup relationships in your database and how it applies to the contacts and companies modules in your CRM. In that example, a company can have many contacts, but a contact could theoretically exist without a relationship with any company.
Parent-Child Relationship Examples
Using the Deals module in a CRM
A Deal should only exist in your database if it has an existing Company or Contact associated with it. Otherwise, it does not make sense in the context of your business (or your database). It is called the Parent-Child Relationship when a record's existence depends on the existence of its parent record. Parent-Child Relationship when a record's existence depends on the existence of its parent record.
In this example, the Deal record is the Child and the Company record is the parent. You could have many Deals with the same client, but you should only have one client per deal.
Project Management Applications
Another example of the parent-child relationship occurs within a project management application such as Zoho Projects. A task record can only exist within a task-list, and a task-list can only exist within a project. There can be many tasks in a task-list, and many task-lists within a project. In this case, the task record is a child record of the task-list parent, and that task-list is a child of the parent project.
Relationships Across Modules or Within a Module
The parent child relationship connect two modules - creating a relationship across modules. Alternatively, you can have parent-child relationships within the same module.
For example, a task record can have numerous associated sub-tasks. These subtasks are child records of the parent task, so they are in the same module.
This relationship also applies to subforms. A subform is a set of fields that repeats in rows, allowing you to easily create numerous child records within a single record. In the example below, there are a primary product (Parent) of "mobile phone" and the subform contains details about the associated accessories (child products).
Another example is within an Invoice record. The Invoice items subform allows you to add multiple line items to your invoice.
Using Subforms
There are two ways subform data can be handled in a database application. One way is to store the subform data as part of the parent record. The other way is to store each subform row as its own record in a child module. In our example with Invoices, the Invoice Items subform is viewed in the database as a separate module. This is considered a child module because an Invoice Item record can only exist within a parent Invoice record.
Each Invoice Item is related back to its parent record via a one-to-many lookup relationship. Each Invoice can have several line items, but each Invoice line item can only apply to one parent Invoice.
Subforms that handle financial data, such as Quotes, Invoices, or Sales Orders (CPQ) often use the sub-module type of subform because it allows for more detailed financial reporting and analytics. Tracking individual line items within each transaction is essential for measuring the profitability of a particular product or service.
Stay Tuned for the next post about database basics!
