CODEWITHSUNDEEP
sql-servert-sqlhierarchical-datasql-graph
Glock
Glock

Reputation: 63

SQL Server : graph/hierarchy SQL structure

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

enter image description here

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

Upvotes: 0

Views: 345

Answers (2)

Shahid Roofi Khan
Shahid Roofi Khan

Reputation: 1037

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

Upvotes: 0

Hasan Mahmood
Hasan Mahmood

Reputation: 978

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.

Upvotes: 0

Related Questions