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.
我有一个节点列表，假设它们是全球机场(SFO, LAX, CDG, HKG等)。节点与具有不同权重的双向边相连。
For example, SFO->LAX edge may be 10, but LAX->SFO is 8.
例如，SFO->LAX edge可能是10，但LAX->SFO是8。
The weights change daily, and I want the mysql database to store all the nodes, edges, and weights per day.
权重每天都在变化，我希望mysql数据库能够存储每天的所有节点、边和权值。
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 ... ... ...
... ... ... ...
2 个解决方案
#1
2
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 ...
#2
0
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
例如，SFO->LAX edge可能是10，但LAX->SFO是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.
虽然您可以使用关系数据库来描述数据，但它并不提供将数据分析为图的工具。然而，有一个MySQL的数据引擎也能做到这一点，但是OQGraph的开发现状是未知的。
#1
2
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 ...
#2
0
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
例如，SFO->LAX edge可能是10，但LAX->SFO是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.
虽然您可以使用关系数据库来描述数据，但它并不提供将数据分析为图的工具。然而，有一个MySQL的数据引擎也能做到这一点，但是OQGraph的开发现状是未知的。