SQL Server : graph/hierarchy SQL structure

Question

After N hours of designing, how to save structure like this into a relational database (SQL Server).

I ended on this structure, but it is not really good.

create table [OperationalModel]
(
    [Id] int,
    [Name] varchar(150),
    [Code] varchar(10),
    [OrgId] int,
    [Vertex] int,
    [RelatedOrgIdOnSameVertex] int
);

insert into [dbo].[OperationalModel] 
values
    (1, 'x', 1, NULL),
    (1, 'x', 2, 1),
    (1, 'x', 3, 1),
    (1, 'x', 4, 2, 3),
    (1, 'x', 5, 2),
    (1, 'x', 6, 2),
    (1, 'x', 7, 3),
    (1, 'x', 8, 4);
.
.
.
.

Anyone have better idea how to save this structure in a relational database like SQL Server?

Thanks

What version of SQL Server are you on? I ask because in SQL 2017, they added Graph extensions to the engine. But even if that's not an option for you, it's hard to judge a table w/o knowing what sorts of queries it needs to support. Another question that comes to mind: is your graph directed or not? That is, are those relationships hierarchical (i.e. parent/child) or do those relationships represent more of a peer model? — Ben Thul, Mar 19 '19 at 15:27
This would be a simple `1:n`-hierachy, if there wasn't node 4, which has two parents. Correct so far? Your picture looks, as if there were hierarchy levels. Is there a known maximum depth? Can the relations ignore the levels (e.g. a line between 4 and 9)? You might use two tables and store the relations in `from -> to` format. Surely there are some business rules you should explain in detail. But - to be honest - this question might be closed as *out of scope of SO*... — Shnugo, Mar 19 '19 at 16:38
At first I wanted to approach it like a tree. The entrance will be two nodes(ids), one main and the other one. The result should be to decide if a other node is a subordinate or a superior node to main node. I will need to evaluate it 600 times per second. — Glock, Mar 19 '19 at 18:20
How often will the hierarchy change and how large will it be? If you need to be getting inferior/superior results in 1ms or less it may make more sense to denormalize your data and compute the relationships when the graph is updated rather than performing traversals on demand. — HABO, Mar 20 '19 at 02:38
Good question, for now occasionally but in the near feature approx 4 times per minute. — Glock, Mar 20 '19 at 08:44

Hasan Mahmood · Accepted Answer · 2019-03-19T15:14:22.100

you need two table. 
one is for the nodes: 
Table: OperationalModel: 
Columns: 
[Id] int,
[Name] varchar(150),
[Code] varchar(10),

another is the edges: 
Table: Edges: Columns: 
Id, OperationalModelId, VertexId, Etc ... 

Data: insert into [dbo].[OperationalModel] values
(1,'x',1),
(1,'x',2),
(1,'x',3),
(1,'x',4),
(1,'x',5),
(1,'x',6),
(1,'x',7),
(1,'x',8);

Data For second table: As per the relationship.
insert into [dbo].[Edge] values
(1,1,2),
(2,1,3),
(3,2,1),
(4,2,4),
(5,3,1),
(5,3,4),
(5,3,5);

The Edge table will be Many to many relationship.

score 0 · Answer 2 · answered Feb 16 '22 at 11:50

You are referring SQL server.

So if you are using SQL 2017, then use Graph database support built into it. You will be able to leverage the query system already there.

https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview?view=sql-server-ver15

SQL Server : graph/hierarchy SQL structure

2 Answers2