HubSpot HubDB and HubL for Dynamic Multi-Level Pages

[September 2023]

A Tutorial with Full Code

Storing data in parent and child tables is a common practice in database design and is applicable in various business scenarios. Here are some common situations where a business may want to use parent and child tables:

  1. Events and Registrations: As you mentioned, events are a great example. The parent table could store general event information such as event name, date, location, and organizer details. Child tables can be used to store registration data, attendee information, and RSVP statuses. This structure allows for easy tracking of event registrations and attendee details while keeping them linked to the specific event.
  2. Orders and Order Items: In e-commerce, the parent table might represent an order, containing information like order number, customer details, and shipping information. Child tables can store individual line items, each with details like product name, quantity, price, and total. This structure allows for a detailed breakdown of order contents while maintaining a link to the overall order.
  3. Projects and Tasks: For project management, the parent table can hold high-level project information, such as project name, start date, and project manager. Child tables can store individual tasks related to the project, including task name, due date, assignee, and status. This setup enables better project tracking and task management.
  4. Customer and Orders: In a CRM system, the parent table could represent customers, storing customer information like name, contact details, and account history. Child tables can be used to store individual orders associated with each customer, including order date, order total, and items purchased. This structure helps track customer order history and preferences.
  5. Content Management: For content-heavy websites, the parent table may represent articles or pages, containing general information such as title, author, and publication date. Child tables can store comments, revisions, or related media files for each article. This design facilitates content organization and retrieval.
  6. Inventory Management: In a retail or warehouse system, the parent table can represent product listings with details like SKU, name, and price. Child tables can store inventory records, tracking stock levels, locations, and transaction history for each product. This structure aids in managing inventory efficiently.
  7. Surveys and Responses: In survey applications, the parent table can store survey details, such as the questionnaire and survey creator. Child tables can capture individual responses, recording respondent details, answer choices, and timestamps. This setup helps analyze survey data and individual responses.
  8. Employee Records and Payroll: HR systems often use a parent table for employee records, including personal details and employment history. Child tables can maintain payroll records, tracking salary information, tax deductions, and payment history. This structure simplifies payroll processing and reporting.

By using parent and child tables, businesses can organize and manage data in a structured and efficient manner. It allows for better data integrity, easier data retrieval, and the ability to maintain relationships between different types of information. This design approach is commonly employed in relational database systems to handle complex data scenarios.

In this example, we store events and registrants in HubDB tables.

Step 1:

Create the Parent and Child HubDB tables.

Tip: Export the tables so you can easily reference the column identifiers. 

Step 2:

Create a module in the Design Manager for module.html (HTML + HubL). The basic code will include configuring each level using “dynamic_page_route_level” HubL variable. The parent or top level page uses a value of “0”.

The code below is the basic code to achieve this.

{% if dynamic_page_route_level == 0 %}
	Top Level Template
{% elif dynamic_page_route_level == 1 %}
	Parent table template (/food /beverage)
{% elif dynamic_page_route_level == 2 %}
	Child table template (/food/banana etc., /beverage/soda etc.)
{% endif %}

Step 3:

To continue populating the page you will pull information from the parent table. You will retrieve this using “dynamic_page_hubdb_table_id. ”

This is where I personally started getting a little confused by HubSpot’s example with food categories on this help document.

The example didn’t fit what we were trying to accomplish with our client who needed a parent table of all the companies that were going through a certification program and the child tables to store the historical certification records for those companies.

In case it does make sense to you, I have pasted the level 1 and 2 code provided by HubSpot below.

{% if dynamic_page_route_level == 1 %}
	<h1>Categories</h1>
	<h2>{{dynamic_page_hubdb_row.hs_name}}</h2>
    {% set rows = hubdb_table_rows(dynamic_page_hubdb_row.hs_child_table_id) %}
    {% for row in rows %}
    	<li><a href="{{ request.path }}/{{ row.hs_path }}">{{ row.hs_name }}</a></li>                
    {% endfor %}
{% elif dynamic_page_route_level == 2 %}
	<h1>Categories</h1>
    <h2>{{dynamic_page_hubdb_row.hs_parent_row.hs_name}}</h2>
    <h3>{{dynamic_page_hubdb_row.hs_name}}</h3>
{% endif %}

Step 4:

Within the module, you will want to create a Data Source for the module. For this, select that you would like the user to select a HubDB table and set the default to the Parent table.

When you add the module to a page, you will also want to select in the “Settings > Advanced” tab that the Data Source for the dynamic page is the Parent Table.