I am looking into using SQL Server 2017's SQL Graph feature to model a graph that has different node types and edge types, but am confused as to how I would model this. In short, a user can create a project that is a collection of connected nodes. There are different types of nodes, where each type of node has some attributes in common (say 5 or so), but has many unique attributes (say 10-25).
Moreover, the nodes are connected by edges, but there are different types of edges that can be used to make the connection, where (like nodes) each type of edge has a few attributes in common, but many more unique attributes.
For simplicity's sake, imagine that there is just one type of edge and only two types of nodes: NodeA
and NodeB
. My initial thought is that I'd:
- Create two node tables
- Create one edge table
E.g.:
NodeA (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeA_Attribute1, string
NodeA_Attribute2, string
...
NodeB (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeB_Attribute1, string
NodeB_Attribute2, string
...
Edge (AS EDGE)
--------------
If the user has a project that has two nodes - one NodeA
and one NodeB
that are connected, then the INSERT
statements would be like so:
INSERT INTO NodeA(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeA', '...')
INSERT INTO NodeB(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeB', '...')
INSERT INTO Edge VALUES((SELECT $node_id FROM NodeA WHERE ID = 1), (SELECT $node_id FROM NodeB WHERE ID = 1))
But here is where I am lost... How do I reconstruct the graph for this particular project? I could query each possible node table, e.g.:
SELECT * FROM NodeA WHERE ProjectID = 123
SELECT * FROM NodeB WHERE ProjectID = 123
But that doesn't seem practical or efficient. My project in reality has ~30 different types of nodes, so running 30 SELECTs (and having to add new SELECTs if a new node type gets added) seems incorrect.
It feels like I'm missing something fundamental here. Is my proposed design incorrect? Are there different ways of querying the SQL Graph that I'm overlooking?
Thanks