SQL Graph Efficiency
Background:
I am using SQLite3 to represent a directed graph.
Scenario 1:
I have two table开发者_StackOverflows: Node and Association
The Node table holds a node ID and data relevant to the particular Node.
The Association table holds a Parent Node Field and a Child Node Field (and an primary key ID).
Scenario 2:
I have one table: Node
The Node table holds a node ID, relevant node data, and a ton of Node Association columns (about 100) to hold the ID of another Node.
Question:
Which is more efficient? Is there a particular graph size that one becomes better than another? Any experience in the scalability of these two options?
My most common query will be determining all nodes that one particular node is connected to.
Go with scenario 1.
Scenario 2 violates basic normalization guidelines and you will quickly be banging your head against a wall when you need to support more than 100 associations. Its also harder to do proper SQL against to analyze the relationships.
Quick! How many nodes have bewteen 10 and 20 associations? That query is a nightmare with scenario 2. Easy with scenario 1.
精彩评论