"what is the best way to represent a many-to-many relationship between records in a single sql table?" Code Answer
4
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:
insert
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:
define a constraint:
entity_id_a < entity_id_b.create indexes:
second index doesn't need to include
entity_id_aas you will use it only to select alla's within oneb.range scanonix_bwill be faster than askip scanonix_a_b.populate the table with your entities as follows:
then select:
union allhere 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