CODEWITHSUNDEEP
mysqlsqldatabase
Ray J Tong
Ray J Tong

Reputation: 897

How do I create a MYSQL Schema to represent a Graph Database?

Looking for some advice on how to map out my table schema to represent a current bi-directional graph.

I have a list of nodes, let's say they are worldwide airports (SFO, LAX, CDG, HKG, etc). The nodes are connected with bi-directional edges with different weights.

For example, SFO->LAX edge may be 10, but LAX->SFO is 8.

The weights change daily, and I want the mysql database to store all the nodes, edges, and weights per day.

This is my current idea, but is there any better method to approach this? Should I use multiple tables?

DATE, SOURCE, DESTINATION, WEIGHT
12/01 LAX     SFO          8
12/01 SFO     LAX          10
12/01 ...     ...          ...
12/02 LAX     SFO          15
12/02 SFO     LAX          9
12/02 ...     ...          ...
...   ...     ...          ...

Upvotes: 4

Views: 8871

Answers (2)

Alexander Hartmann
Alexander Hartmann

Reputation: 121

Create a Node Table and an Edge Table

Node Table
ID Airport AirportProperties
1  LAX     Address of LAX
2  SFO     Adress and Link etc.
3  ...


Edge Table
ID NodeID refNodeID Date  Weight
1  1      2         12/01 8 
2  2      1         12/01 10
3  ...

Upvotes: 3

symcbean
symcbean

Reputation: 48387

Your data model depends on what data you want to store and how you want to interact with it - you only appear to have given us a partial picture of the former. Although a single table can describe the edges, it is directional, and therefore requires 2 rows to describe each edge - as per your example.

For example, SFO->LAX edge may be 10, but LAX->SFO is 8

This states that the data is not symmetric and therefore not bi-directional - in which case the single table suffices.

While you can describe the data in terms of a relational database, it does not provide the tools for analysing the data as a graph. However there is a data engine for MySQL which does exactly that, however the current status of development of OQGraph is unknown.

Upvotes: 0

Related Questions