Graph Polymorphism Design in SQL Server 2017

graphsql-server-2017

I have a graph database with multiple edge types. For example

create table Person(Name varchar(50)) as node;
create table Friend as edge;
create table Manager as edge;

insert Person(Name) values ('Alice'), ('Bob'), ('Chris');

insert Friend($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob');

insert Manager($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris');

I can find all the nodes connected to a given node (graph polymorphism) through UNION queries:

select
    b.Name
from Person a, Person b, Friend f
where a.Name = 'Alice'
and match(a-(f)->b)

union

select
    b.Name
from Person a, Person b, Manager m
where a.Name = 'Alice'
and match(a-(m)->b);

However, this rapidly becomes cumbersome. SQL Server does not have a more elegant solution built into the syntax.

Is there a design pattern which allows for polymorphism?

Best Answer

The disparate edges tables can be combined into a single table, holding all associations:

create table Connection(ConnectionType varchar(20)) as edge;

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob'),
    'Friend';

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris'),
    'Manager';

This table can be queried to retrieve all associations

select
    b.Name,
    c.ConnectionType
from Person a, Person b, Connection c
where a.Name = 'Alice'
and match(a-(c)->b);

Giving

Name     ConnectionType
-------  --------------------
Bob      Friend
Chris    Manager

Column ConnectionType is optional, but allows for the same level of specificity as the original schema did.

This approach is analagous to an EAV design, and likely to suffer the same performance issues at scale. Filtered indexes will help

create nonclustered index IX_Friend on Connection($from_id, $to_id)
where ConnectionType = 'Friend';
Related Question