Указание канонической записи в таблице типа "один ко многим"

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

countries
| id | name    |
|  1 | Lorwick |
|  2 | Belmead |

cities
| id | country | name        |
|  1 |       1 | Marblecrest |
|  2 |       1 | Westacre    |
|  3 |       2 | Belcoast    |
|  4 |       1 | Rosemarsh   |
|  5 |       2 | Vertston    |

Но в дополнение к нашим отношениям «один-ко-многим» мы хотим описать взаимно-однозначное отношение национальных столиц. Если это имеет значение, предположите, что столицы могут меняться довольно регулярно, и в этом отношении города появляются и исчезают по желанию, и что города могут переключаться между странами. Дело в том, что эти данные нестабильны.

Вижу пару вариантов:

  1. Добавьте столбец типа int capital в countries, который не может быть нулевым. Плюс: всегда ровно один город; Против: не связано с городом, в стране нет ничего, что заставляет город, или что он вообще существует.

  2. Добавьте логический столбец capital в cities, который, если истина, указывает, что город является столицей связанной страны. Плюсы: напрямую связаны с рассматриваемым городом, нет повторяющихся столбцов, указывающих на иерархию; Против: почти уверен, что это плохая нормализация, поскольку ничто не мешает иметь ноль или более одного «капитала» для данной страны.

  3. Создайте дополнительную таблицу capitals со столбцами country и city и уникальным ограничением для обоих столбцов (или, по крайней мере, для city). Pro: кажется более чистым, легкое присоединение на countries или cities; Против: все еще не гарантирует, что город находится в стране или что он существует.

Каков самый нормальный и / или лучший способ представить эти отношения? Есть ли способ гарантировать, что каждая страна имеет ровно одну столицу, которая действительно существует и находится внутри этой страны? Я полагаю, что это невозможно, и в таком случае, как мне лучше всего свести к минимуму проблемы для моего клиентского кода?

В настоящее время я использую SQLite, но меня интересуют обобщенные ответы, независимо от базовой базы данных.

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

PS: Не так уж и плохо, если нет столицы (а может и не быть городов!), Но было бы плохо, если бы их было несколько.


sqlite database-design database-normalization
person dimo414    schedule 16.08.2012    source источник

Ответы (3)


arrow_upward
1
arrow_downward

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

Вы можете установить ограничение «каждая страна имеет [ноль или] один капитал, который на самом деле существует и находится внутри этой страны» с ограничением внешнего ключа для таблицы капиталов.

create table capitals (
  country_id integer primary key,
  city_id integer not null,
  foreign key (country_id, city_id) references cities (country_id, city_id)
);

В этой таблице ограничение первичного ключа гарантирует, что в каждой стране может быть не более одной столицы. Ограничение внешнего ключа гарантирует, что выбранный вами капитал существует в выбранной вами стране. В указанной таблице (таблица "города") вам также необходимо уникальное ограничение на {city_id, country_id}; так как {city_id} уникален в таблице "cities", {city_id, country_id} обязательно будет уникальным и в этой таблице, так что это не проблема.

Декларативный «способ» гарантировать взаимно однозначное отношение между странами и столицами (а не соотношение один к нулю или одному) заключается в использовании утверждения. Но я не знаю ни одной базы данных SQL, поддерживающей CREATE ASSERTION. Это заставляет нас полагаться на одно или несколько из них:

  • триггеры и, возможно, отложенные ограничения,
  • код приложения, или
  • административные процедуры.

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

person Mike Sherrill 'Cat Recall'    schedule 16.08.2012

arrow_upward
0
arrow_downward

Для ясности и простоты я бы добавил логический столбец IsCapital в таблицу городов. Затем добавьте триггер, который устанавливает для всех других городов (которые имеют общую страну обновленной записи) IsCapital = false, если IsCapital имеет значение true для записи. Это решит большинство ваших проблем. Единственный случай, чтобы гарантировать, что для каждой страны есть ровно одна столица, на самом деле невозможно, вы можете убедиться, что есть 0 или 1, но поскольку таблица городов имеет ограничение FK для стран, всегда будет момент времени, когда во вставленных странах не будет городов, которые можно назначить столицей.

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

person JeremyWeir    schedule 16.08.2012
comment
Я только что поискал похожие вопросы и нашел этот ответ stackoverflow.com/a/638947/45767 (его ответ подтверждает, почему Я думаю, что логика принадлежит приложению) - person JeremyWeir; 16.08.2012

arrow_upward
0
arrow_downward

Чтобы убедиться, что в каждой стране есть только одна столица, а столица не принадлежит к другой стране, сделайте следующее:

введите описание изображения здесь

Обратите внимание, как мы используем отношение идентифицирующее для переноса COUNTRY_ID в PK CITY, чтобы его можно было перенести обратно в CONTRY - это то, что гарантирует, что капитал действительно должен принадлежать стране, столицей которой он является.

Циклическая ссылка здесь предотвращает вставку новых данных, которая разрешается с использованием отложенных внешних ключей, если СУБД поддерживает их. В противном случае вы можете просто оставить COUNTRY.CAPITAL_NO NULL-совместимым (и обеспечить его возможное ненулевое значение на уровне приложения). 1

1 Предполагается, что СУБД имеет внешние ключи MATCH SIMPLE (т.е. FK игнорируется, если какой-либо из его компонентов равен NULL). Если СУБД поддерживает только MATCH PARTIAL или FULL (например, MS Access), вам не повезло, и вам придется эмулировать FK с помощью недекларативных средств (триггеры или код приложения).

person Branko Dimitrijevic    schedule 21.08.2012