From relational to graph databases

Due to the poor performance of our relational database in answering graph-like questions, we may want to move our tabular data into a graph format.

First, we will consider a sensible graph schema for our data, based on the information we have available, before writing a pipeline to move data from MySQL into a Python igraph network. By doing this, we can benchmark how a graphical approach to our path-based question performs, in comparison to the same question we answered with SQL.

Schema design

In our tables, we have two types of entities, users and games, which have different properties. Because of this, it is wise to consider users and games as different node types.

For users, we only have a unique ID for each user. To add data to an igraph graph, we will need to add an increasing integer igraph node ID for each distinct node, as we learned in Chapter 1, Introducing Graphs in the Real World, and Chapter 2, Working with Graph Data Models.

On the other hand, for games, we are given the game’s name as a string. We must also add an igraph ID to distinct game nodes in the same manner as for user nodes.

We have already considered the schema design for nodes, but we must also consider how we should represent relationships. For our purposes, we are interested in the interactions between users and games. We have information on games purchased by users, as well as the length of time a user has played a game for.

Because of this, it is sensible to consider these different interactions as different edge types. Purchasing a game is represented just by the existence of a row in the steam_purchase table, and essentially the relationship either exists or doesn’t. We have no additional data to represent this interaction, so for game purchases, there will be no edge properties.

However, for relationships representing play time, we have information on how long a user played a game, in hours. In this case, we can use an edge property to represent the number of hours a game has been played by a user.

Taking all this into account, we can set out a schema for our graph, as follows:

Figure 3.2 – Directed heterogeneous graph schema

Logically, since users either purchase or play games, in the graph schema, both the PLAYED and PURCHASED relationships are directional, from the User node to the Game node. For the remainder of this chapter, we will use this schema, but theoretically, it would make no difference if the relationships flowed from Games to Users, and instead had the names PLAYED_BY and PURCHASED_BY. The only downstream difference with this design would be that the direction of edges in any interrogation of the graph would need to be reversed.