Вопрос о схеме базы данных

Наиболее близким к отраслевому стандарту является следующее: каждая зависимая таблица связана внешним ключом со своим непосредственным родителем:

create table country
(country_id number not null
 , country_name varchar2(30)
 , constraint country_pk primary key (country_id)
 )
/
create table state
(state_id number not null 
 , state_name varchar2(30)
 , country_id number not null
 , constraint state_pk primary key (state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(city_id number not null 
 , city_name varchar2(30)
 , state_id number not null 
 , constraint city_pk primary key (city_id)
 , constraint city_state_fk foreign key (state_id)
        references state(state_id)
 )
/
create table neighbourhood
(neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , city_id number not null 
 , constraint neighbourhood_pk primary key (neighbourhood_id)
 , constraint neighbourhood_city_fk foreign key (city_id)
        references city(city_id)
 )
/

Альтернативный подход, который в значительной степени потерял популярность , состоит в том, чтобы определить первичные ключи дочерних таблиц как составные ключи, включая ключи непосредственной родительской таблицы:

create table state
(country_id number not null
 , state_id number not null 
 , state_name varchar2(30)
 , constraint state_pk primary key (country_id, state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(country_id number not null
 , state_id number not null 
 , city_id number not null 
 , city_name varchar2(30)
 , constraint city_pk primary key (country_id, state_id, city_id)
 , constraint city_state_fk foreign key (country_id, state_id)
        references state(country_id, state_id)
 )
/
create table neighbourhood
(country_id number not null
 , state_id number not null 
 , city_id number not null 
 , neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , constraint neighbourhood_pk primary key (country_id, state_id, city_id, neighbourhood_id)
 , constraint neighbourhood_city_fk foreign key (country_id, state_id, city_id)
        references city(country_id, state_id, city_id)
 )
/

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

Ваше предложение является альтернативной версией этого:

create table state
(state_id number not null 
 , state_name varchar2(30)
 , country_id number not null
 , constraint state_pk primary key (state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(city_id number not null 
 , city_name varchar2(30)
 , country_id number not null
 , state_id number not null 
 , constraint city_pk primary key (city_id)
 , constraint city_country_fk foreign key (country_id)
        references country(country_id)
 , constraint city_state_fk foreign key (state_id)
        references state(state_id)
 )
/
create table neighbourhood
(neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , country_id number not null
 , state_id number not null 
 , city_id number not null 
 , constraint neighbourhood_pk primary key (neighbourhood_id)
 , constraint neighbourhood_country_fk foreign key (country_id)
        references country(country_id)
 , constraint neighbourhood_state_fk foreign key (state_id)
        references state(state_id)
 , constraint neighbourhood_city_fk foreign key (city_id)
        references city(city_id)
 )
/

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

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

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

править

«Состояние должно быть необязательным, поскольку не во всех странах есть государство. Потом Страна соединится с городом напрямую. "

Если true, это меняет все. Очевидно, что STATE нельзя использовать в качестве идентифицирующего внешнего ключа для CITY. Поэтому CITY должен вместо этого ссылаться на COUNTRY. STATE может быть дополнительным поиском для CITY.

Хотя я думаю, что большинство стран действительно есть некоторые эквивалентные подразделения, такие как округа или департаменты. Даже в микрогосударствах, таких как Лихтенштейн и Сан-Марино, есть муниципалитеты (в Монако есть только один). Возможно, единственная страна, которой нет, - это Ватикан. Поэтому тщательно подумайте, стоит ли структурировать свои модель данных для поддержки одного или двух крайних случаев или для изменения данных путем введения искусственного "государства" для исключений, таких как Святой Престол. Ни один из подходов не является полностью удовлетворительным.

"все эти поля будут поля автозаполнения, поэтому не уверен, что который изменяет структуру таблицы в в любом случае? "

Без разницы.

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

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

И если оно действительно появится, то первым решением будет объединение NEIGHBORHOOD и COUNTRY через промежуточные таблицы (или таблицы, если вы не используете STATE как указано для CITY). Только если производительность этого запроса равна Teh Suck! и он упорно сопротивляется настройке, следует подумать о настройке модели данных.