0

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

0

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.

| improve this answer | |
  • What are the advantages of using SQL Graph over "doing it myself?" I understand SQL Graph adds some query syntax like MATCH and MERGE, but if I'm going all the "heavy lifting" in the application layer and only using the DB for persisting the graph topology (e.g., not querying it beyond just pulling all nodes/edges in the graph) is there any reason to use SQL Graph? – Scott Mitchell Jul 31 '19 at 21:34
  • In addition to the query syntax for graph traversal and retrieval, SQL Server graph allows any edge to connect any two nodes, regardless of node type, and optionally add Edge Constraints to restrict this. There's no first-class way to do this with ordinary tables and foreign keys. You'd need a master Node table, with a multiple Node "subtype" tables, and a bunch of extra logic to implement this yourself. But like lots of other SQL Server features, there's nothing you really can't do yourself. It just may be cleaner and easier to use the built-in feature. – David Browne - Microsoft Jul 31 '19 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.