How do I Create a Table for Tree Data

Introduction
In Oracle APEX, working with hierarchical or tree-structured data often starts with designing the underlying database table appropriately. Whether you're building a tree report, organization chart, or category structure, a well-structured table is critical for enabling parent-child relationships. In this blog post, we’ll walk through how to create a table designed for tree data, explain the required columns, and offer tips for managing and querying hierarchical data effectively.

How to Create a Table for Tree Data

To support tree-based UI components in Oracle APEX, your table should include at least the following:

• A unique identifier (ID) – Primary key for each row

• A parent reference (PARENT_ID) – Self-referencing foreign key that defines the hierarchy

• A label or name (NAME) – Human-readable name for display purposes

• Optional: NODE_TYPE, ICON_CLASS, DESCRIPTION, or IS_LEAF – for advanced visualization or logic

Step-by-Step SQL to Create the Table:

CREATE TABLE TREE_STRUCTURE (
  ID          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  PARENT_ID   NUMBER REFERENCES TREE_STRUCTURE(ID),
  NAME        VARCHAR2(100) NOT NULL,
  DESCRIPTION VARCHAR2(4000),
  CREATED_AT  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Example Data:

INSERT INTO TREE_STRUCTURE (NAME, PARENT_ID) VALUES ('Root Node', NULL);
INSERT INTO TREE_STRUCTURE (NAME, PARENT_ID) VALUES ('Child A', 1);
INSERT INTO TREE_STRUCTURE (NAME, PARENT_ID) VALUES ('Child B', 1);
INSERT INTO TREE_STRUCTURE (NAME, PARENT_ID) VALUES ('Sub-child A1', 2);

This example creates a simple 3-level hierarchy:

• Root Node
  ├── Child A
  │   └── Sub-child A1
  └── Child B

Best Practices

• Ensure ID is a primary key and PARENT_ID references the same table to maintain integrity.

• Use indexed columns on PARENT_ID for better performance in CONNECT BY queries.

• Avoid circular references by validating data before inserts or updates.

• Use a VIEW to add virtual columns such as full path, depth, or breadcrumb if needed.

Oracle APEX Documentation
For more information on working with tree data and reports, visit:
Oracle APEX Tree Region Documentation

 

Before creating the report, ensure that your table follows a hierarchical structure. The table should have at least two key columns:

1. ID – The unique identifier for each node.

2. PARENT_ID – The reference to the parent node.

Example table structure:

CREATE TABLE DEPARTMENT_HIERARCHY (

    DEPT_ID      NUMBER PRIMARY KEY,

    DEPT_NAME    VARCHAR2(100),

    PARENT_ID    NUMBER REFERENCES DEPARTMENT_HIERARCHY(DEPT_ID)

);

Sample data:

INSERT INTO DEPARTMENT_HIERARCHY VALUES (1, 'Corporate', NULL);

INSERT INTO DEPARTMENT_HIERARCHY VALUES (2, 'Finance', 1);

INSERT INTO DEPARTMENT_HIERARCHY VALUES (3, 'HR', 1);

INSERT INTO DEPARTMENT_HIERARCHY VALUES (4, 'Payroll', 2);

INSERT INTO DEPARTMENT_HIERARCHY VALUES (5, 'Recruitment', 3);

COMMIT;

---

Creating a Tree Report in Oracle APEX

1. Go to Oracle APEX and navigate to your application.

2. Click Create → Page → Report → Tree.

3. Select Use a SQL Query as the data source.

4. Enter the SQL Query for the tree structure:

SELECT 

    DEPT_ID AS ID,

    PARENT_ID AS PARENT_ID,

    DEPT_NAME AS NAME

FROM DEPARTMENT_HIERARCHY

5. Click Next, configure the report settings, and click Create.

After creating the tree, you can customize how it appears and behaves.

Configuring Tree Attributes

1. Open Page Designer and select the Tree Region in the Rendering Pane.

2. Modify the following attributes under the Tree Attributes section:

• Icon Column: Specify an icon for nodes (e.g., fa-folder for parent nodes).

• Expand All: Enable this if you want the tree fully expanded by default.

• Lazy Loading: Set this to Yes if working with large datasets to improve performance.

• Selection Type: Choose Single Selection if you want users to select only one node.

Conclusion
Creating a table that supports tree data in Oracle APEX involves a simple yet structured approach. By establishing a self-referencing schema with proper constraints and meaningful attributes, you set the foundation for effective tree visualizations. With this setup, you can build dynamic tree reports, enable drill-down features, and maintain hierarchical clarity within your APEX applications.