Modeling a graph of different node types and edge types using SQL Graph

Question

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

Answer 1

How do I reconstruct the graph for this particular project?

To send data from SQL Server to the client, you use TDS, which means one result set per table "shape", or use JSON/XML encoded in a unicode string.

So yes, if you needed all the details for each node type, you would have one query per node type. You can join the different tables together into fewer queries (as in EF Eager Loading), but that doesn't really improve the efficiency.