Что лучший способ состоит в том, чтобы представить many-many отношения между записями в единственной таблице SQL?

Я думаю структура, которую Вы предложили, прекрасен.

Для получения связанных записей делают что-то как

SELECT related.* FROM entities AS search 
LEFT JOIN entity_entity map ON map.entity_id_a = search.id
LEFT JOIN entities AS related ON map.entity_id_b = related.id
WHERE search.name = 'Search term'

Надежда, которая помогает.

Подход таблицы ссылки кажется прекрасным, за исключением того, что Вы могли бы хотеть 'тип связей' так, чтобы Вы знали, ПОЧЕМУ они связаны.

Например, отношение между Роли и Северной Каролиной не является тем же как отношением между Роли и Даремом. Кроме того, можно хотеть знать, кто 'родитель' в отношениях, в случае, если Вы управляли условным выражением, выпадает. (т.е. Вы выбираете состояние, Вы добираетесь для наблюдения городов, которые находятся в состоянии).

В зависимости от сложности Ваших требований простая установка, которую Вы имеете прямо сейчас, не может быть достаточной. Если просто необходимо показать, что две записи связаны в некотором роде, таблица ссылки должна быть достаточной.

Я уже отправил способ сделать это в Вашем дизайне, но я также хотел предложить это отдельное понимание дизайна, если у Вас есть некоторая гибкость при Вашем разработке, и это более тесно соответствует Вашим потребностям.

Если объекты находятся в (неналожении) классов эквивалентности, Вы могли бы хотеть сделать классы эквивалентности основанием для дизайна таблицы, где все в классе считают эквивалентным. Сами классы могут быть анонимными:

CREATE TABLE equivalence_class (
    class_id int -- surrogate, IDENTITY, autonumber, etc.
    ,entity_id int
)

entity_id должно быть уникальным для неперекрывающегося раздела Вашего пространства.

Это избегает проблемы обеспечения надлежащего лево-или праворукость или принуждение верхней правой матрицы отношений.

Затем Ваш запрос немного отличается:

SELECT c2.entity_id
FROM equivalence_class c1
INNER JOIN equivalence_class c2
    ON c1.entity_id = @entity_id
    AND c1.class_id = c2.class_id
    AND c2.entity_id <> @entity_id

или, эквивалентно:

SELECT c2.entity_id
FROM equivalence_class c1
INNER JOIN equivalence_class c2
    ON c1.entity_id = @entity_id
    AND c1.class_id = c2.class_id
    AND c2.entity_id <> c1.entity_id

Я могу думать о нескольких путях.

Единственная передача со СЛУЧАЕМ:

SELECT DISTINCT
    CASE
        WHEN entity_id_a <> @entity_id THEN entity_id_a
        WHEN entity_id_b <> @entity_id THEN entity_id_b
    END AS equivalent_entity
FROM entity_entity
WHERE entity_id_a = @entity_id OR entity_id_b = @entity_id

Или два фильтрованных запроса UNIONed таким образом:

SELECT entity_id_b AS equivalent_entity
FROM entity_entity
WHERE entity_id_a = @entity_id
UNION
SELECT entity_id_a AS equivalent_entity
FROM entity_entity
WHERE entity_id_b = @entity_id

select * from entities
where entity_id in 
(
    select entity_id_b 
    from entity_entity 
    where entity_id_a = @lookup_value
)

На основе Вашей обновленной схемы должен работать этот запрос:

select if(entity_id_a=:entity_id,entity_id_b,entity_id_a) as related_entity_id where :entity_id in (entity_id_a, entity_id_b)

где: entity_id связывается с объектом, который Вы запрашиваете

Мой совет состоит в том, что Ваш начальный дизайн таблицы плох. Не храните различные типы вещей в той же таблице. (Первое правило проектирования баз данных, право там с не хранит несколько сведений в том же поле). Это намного более трудно запросить и вызовет значительные проблемы производительности в будущем. Плюс он была бы проблема вводящий данные в таблицу отношений - как Вы знаете, какие объекты должны были бы быть связаны, когда Вы делаете новую запись? Было бы намного лучше разработать правильно реляционные таблицы. Таблицы объекта являются почти всегда плохой идеей. Я не вижу оснований вообще от примера, чтобы иметь этот тип информации в одной таблице. Откровенно у меня были бы университетская таблица и связанная таблица адресов. Это было бы легкий запросить и работать намного лучше.