"what is the best way to represent a many-to-many relationship between records in a single sql table?" Code Answer


define a constraint: entity_id_a < entity_id_b.

create indexes:

create unique index ix_a_b on entity_entity(entity_id_a, entity_id_b);
create index ix_b on entity_entity(entity_id_b);

second index doesn't need to include entity_id_a as you will use it only to select all a's within one b. range scan on ix_b will be faster than a skip scan on ix_a_b.

populate the table with your entities as follows:

into entity_entity (entity_id_a, entity_id_b)
values (least(@id1, @id2), greatest(@id1, @id2))

then select:

select entity_id_b
from entity_entity
where entity_id_a = @id
union all
select entity_id_a
from entity_entity
where entity_id_b = @id

union all here lets you use above indexes and avoid extra sorting for uniqueness.

all above is valid for a symmetric and anti-reflexive relationship. that means that:

  • if a is related to b, then b is related to a

  • a is never related to a

By Jon Andersen on March 11 2022
Only authorized users can answer the Search term. Please sign in first, or register a free account.