Modeling: Relational to Graph

You may already have an existing project running on a relational database that you'd like to convert to a graph, or you might want to start a new graph project but are more familiar with relational models. Either way, this guide will walk you through the process of easily transforming a relational data model into a graph model.

Introducing the Tables

Assume we have three tables in a retail business: Customer, Merchant, and Transaction. The Transaction table records customers' transaction behaviors with merchants.

Relational Data Model Columns in the Customer Table and Example Data Columns in the Merchant Table and Example Data Columns in the Transaction Table and Example Data

Modeling into a Graph

Graph databases are unlike relational databases that require you to establish connections between entities using foreign keys. Instead, you can model the connections directly as edges in the graph.

Building on the graph structure introduced, here's how the transformation goes:

• Entity tables to node types: Map the Customer and Merchant tables, which represent entities, to node types Customer and Merchant.
• Columns to node properties: Transform the columns in each entity table into properties of the corresponding node type. Use the primary key as the unique identifier _id for each node.x
• Relationship tables to edge types: Map the Transaction table, which defines connections between entities, to an edge type named TransfersTo. Using a verb as the edge type name is recommended, as it better expresses the action and direction of the relationship.
• Columns to edge properties: Transform the columns in each relationship table into properties of the corresponding edge type. Use the foreign keys cust_no and merch_no as the system properties _from and _to, representing the _id of the source and destination nodes, respectively.
• Rows to nodes and edges: Treat each row in the tables as a node or an edge in the graph.

This effectively maps the relational data model to graph structures as shown below:

Graph Structure

And here is the graph produced:

You can refer to Importing Data to Ultipa to learn how to import data into the graph database.

Customizing the Graph Structure

The graph structure is highly flexible and can be tailored to specific analytical or operational requirements. Adjusting the structure helps better align the graph with the focus and goals of your use case.

For example, some scenarios focus on transactions as primary entities that require modeling them as nodes instead of edges. The graph structure can be adjusted as below where the Trasaction table is mapped into the Transaction node type, which connects to the User and Merchant nodes through hasPayer and hasPayee edge types:

Graph Structure: Transactions as Nodes

In cases where there is a need to analyze the types of merchants, you can enhance the graph structure by extracting the type column in the Merchant table and converting it into a separate node type. Instead of storing this information as a property of Merchant nodes, the graph structure would include Type nodes connected to Merchant nodes.

Graph Structure: Merchant Types as Nodes

Ensure the type column is deduplicated before converting rows into Type nodes.