Visualizing Relational Databases

How KeyLines can help you visualize the networks in your relational database

You would be forgiven for thinking that relational databases don’t work for network visualization.

Whilst it’s true that the tabular data model and schema restrictions sometimes make relational databases an inefficient option for visualizing connected data, with the right data modeling it is possible. In fact, sometimes a tabular view may be the most efficient storage and query mechanism.

This page introduces the topic of relational databases, and how they can be used for graph / network visualization.

What is a relational database?

A relational database, as the name suggests, is one storing related data, represented in two-dimensional tables of columns and rows. Each data entity is represented with its attributes in the columns, and data instances in the rows:

Policy_numberPolicy_AddressVehicle_VINVehicle_ModelVehicle_Year
AB123123123 Road Street19UYA1234L000213Coupe2012
ZN 98798742 Mill Crescent1005Estate2007
JP 45645612 Sample Street19UYA1234L000213Coupe2012

Creating a network with SQL Joins

These tables, known as relations, can rapidly grow to millions, or billions of rows. It’s possible to get data from multiple tables. For example, suppose the data shown above was stored in two separate tables:

Policy

IDNumberAddressVehicle_ID
1AB123123123 Road Street1001
2ZN98798742 Mill Crescent1005
3JP45645612 Sample Street1001

Vehicle

IDVINModelYear
100119UYA1234L000213Coupe2012
100514PLU1234L120213Estate2007
100716MYA1234L000215SUV2014

As long as there is a column that appears in both tables (in this case, the vehicle ID), we can cross-reference using an SQL operator called “JOIN”. In the vehicle table, this column is known as the Primary Key; in the policy table it is the foreign key.

If the primary key is deleted or altered, the database may trigger an action on cascade to keep the consistency of the references.

Using the JOIN operator, we can start to build up a complex connected data structure, resembling a network (or graph) that KeyLines can work with. Joins, however, can be expensive in terms of performance.

Optimizing SQL joins for data visualization

Once we understand how a join works, it’s possible to fine-tune the logic to speed up system performance. For example:

SELECT *
FROM Policy JOIN Vehicle
ON Policy.Vehicle_ID = Vehicle.ID

The SQL statement above will search all the Vehicle IDs in the Policy table to find a match in the Vehicles table. If the table contains 1,000,000 rows, the user will have to wait for the system to finish scanning all of them.

Database administrators can overcome this by:

Taking these steps can help ensure database performance isn’t too much of a bottleneck when using a relational datastore for network visualization.

Converting relational data structure to a network

How relational data is modeled into a graph depends entirely on the data being visualized and the questions that need to be answered.

Taking the above vehicle / policy example, there would be four generic steps required:

  1. Understand the data – know which entity attributes and record formats are present in the data, and how they relate to one another.
  2. Combine required data relations – where possible, move the data into a single table to reduce the reliance on joins.
  3. Identify key relationships – understand which relationships users need to understand to answer the questions they are asking.
  4. Model data around key relationships – For example, an insurance fraud analyst may need to identify vehicles with more than one insurance policy, in which case the Vehicle Identification Numbers and Policy Numbers would be mapped to nodes, with the remaining data entities included as properties:
Network visualization makes it easy to collate, explore and communicate complex connected data from multiple data silos.
Network visualization makes it easy to collate, explore and communicate complex connected data from multiple data silos.

When mapping tabular data to a connected graph format, it’s important to consider how to manage IDs – important because KeyLines will automatically merge new data into an existing node if the ID already exists on the chart.

For example, in the case above we could set the vehicle make as the node ID field, so all Nissan vehicles appear as a single node. This could be useful for understanding patterns on a brand level.

Alternatively, the car number plate could be set as the node ID. This way, each unique number plate would appear as a unique node, which is potentially more useful to an analyst. However, it would still be necessary to carefully plan this structure and consider potential anomalies – stolen, shared or transferred number plates, for example.

Relational Database Visualization infrastructure

The architecture for connecting KeyLines to a relational database is relatively simple:

architecture for visualizing a relational database with KeyLines
Visualizing a relational database with KeyLines.

A user interaction in the KeyLines component raises an AJAX request for data from the database, via a REST API. Depending on the choice of database, this might be available out of the box, or need to be configured as part of the set up.

Once the data is retrieved from the database, it is parsed into KeyLines’ own published JSON format and merged or loaded into the KeyLines chart.

Learn more

To learn more about the KeyLines network visualization toolkit and how to visualize your complex connected data, download a copy of our KeyLines white paper.

Download the White Paper

A night at the Oscars with KeyLines and Stardog

This blog post shows how quick and easy it is to integrate Stardog with KeyLines using Academy Awards data.

Visualizing TED Talks data with Memgraph

KeyLines is flexible enough to work with any datastore, but it’s an especially good fit for graph databases. In this blog post, we’re showcasing how to integrate KeyLines with the Memgraph databas

Using Angular CLI to get started with KeyLines

This blog walks you through the commands you need to integrate Angular with KeyLines and start coding.

Visit the blog

Subscribe to our newsletter

Get occasional data visualization updates, stories and best practice tips by email