圖多態性設計

我有一個具有多種邊類型的圖形數據庫。例如

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在語法中沒有內置更優雅的解決方案。

是否有允許多態性的設計模式？

不同的邊表可以組合成一個表，包含所有關聯：

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

引用自：https://dba.stackexchange.com/questions/212758