Иерархическая База данных, несколько таблиц или столбца с родительским идентификатором?

Я бы использовал три отдельные таблицы, поскольку вы точно знаете, с какими категориями информации вы работаете, и вам не нужно динамически изменять «глубину» вашей иерархии.

Это также упростит управление данными, так как вы сможете определить, относятся ли данные к городу, муниципалитету или округу, просто зная таблицу (и без необходимости различать «глубину» запись в иерархии первой!).

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

Если у вас действительно есть ограничение на эти три уровня (округ, муниципалитет, город), я думаю, вы будете счастливее всего с тремя отдельными таблицами с внешними ключами, достигающими одного уровня каждый. Это сделает запросы почти тривиальными для написания.

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

Решение с раздельными таблицами будет гораздо проще в использовании.

County, Municipality и City не похожи на данные одного типа; поэтому я бы использовал три разных таблицы: по одной на каждый тип данных.

А затем я бы действительно использовал внешние ключи между ними.

С точки зрения эффективности, не уверен, что это сильно изменится:

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

Но, с точки зрения структуры, если это три разных типа сущностей, то имеет смысл использовать три разные таблицы.

Я бы рекомендовал использовать три разные таблицы, поскольку это три разные сущности.

Я бы использовал только одну таблицу в тех случаях, когда вы не знаете глубину иерархии, но это не тот случай.

Я бы поместил их в три разные таблицы только на том основании, что это 3 разные концепции. Это снизит скорость и усложнит ваши запросы. Однако, учитывая, что MySQL не имеет какой-либо специальной поддержки для хирахических запросов (например, Oracle connect by оператор), это все равно было бы сложно.

Разные таблицы: это просто "правильно". Я сомневаюсь, что вы увидите какой-либо выигрыш/проигрыш в производительности в любом случае, но это тот случай, когда правильное предварительное моделирование, вероятно, избавит вас от головной боли в дальнейшем. Во-первых, это облегчит написание и чтение SQL SELECT'ов.

Вы получите разные мнения по этому поводу, но я лично предпочитаю иметь отдельные таблицы, потому что они являются отдельными объектами.

На самом деле вам нужно подумать о запросах, которые вы будете делать с этими данными, и обычно ваш ответ будет исходить из них. С отдельными таблицами ваши запросы будут выглядеть намного чище, и в конечном итоге вы ничего не сэкономите, потому что вы все равно будете объединять таблицы вместе, даже если это одна и та же таблица.

В приложениях для размещения программного обеспечения для обработки данных сторонники методологии Кимбалла могут поместить эти поля в одну таблицу атрибутов:

create table city (
   id int not null, 
   county varchar(50) not null,
   municipality varchar(50),
   city varchar(50),
   primary key(id) 
);

Идея состоит в том, что атрибуты никогда не должны располагаться дальше чем l join от таблицы фактов.

Я просто заявляю это как альтернативную точку зрения. Я бы лично выбрал 3-х столовый дизайн.