主页 / dba / 问题 / 212758
Accepted
Michael Green
Asked: 2018-07-21 02:59:45 +0800 CST

图多态性设计

  • 772

我有一个具有多种边缘类型的图形数据库。例如

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');

我可以通过 UNION 查询找到连接到给定节点（图多态性）的所有节点：

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);

然而，这很快变得很麻烦。SQL Server没有内置于语法中的更优雅的解决方案。

是否有允许多态性的设计模式？

graph sql-server-2017

1 个回答

  1. Best Answer

    不同的边缘表可以组合成一个表，包含所有关联：

    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';

    可以查询此表以检索所有关联

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

    给予

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

    列 ConnectionType 是可选的，但允许与原始模式具有相同级别的特异性。

    这种方法类似于 EAV 设计，并且可能在规模上遇到相同的性能问题。过滤索引会有所帮助

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

相关问题