Стоит ли разбить адресную информацию в отдельной таблице базы данных?

Ответ 1

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

Идея нормализации, чтобы избежать аномалий обновления, немного сомнительна. Как часто города, государства или страны фактически меняют имена? Кроме того, если это произойдет, насколько вероятно, что изменение будет оптовой? (т.е. каждый экземпляр старого имени X изменяется на новое имя Y). Я могу рассказать вам, что произошло на практике в Канаде, когда в 2000 году произошел шквал муниципальных слияний, что границы были перерисованы, и что много старых имен застряли вокруг, только с меньшими территориями, чем раньше.

Дело в том, что такие вещи, как названия муниципалитетов, могут быть свободно определены. Например, когда я вырос, мой адрес имел три официально признанных названия муниципалитета в соответствии с почтовым органом: WILLOWDALE, NORTH YORK, TORONTO - все это были действительные варианты, хотя один из них был "более официальным", чем другие. Проблема в том, что все Willowdale находятся в Северном Йорке, но в Северном Йорке также есть "Downsview" и другие.

Другими частыми аргументами для нормализации адресов являются: обеспечение правильной орфографии и обеспечение основы для управления территорией. Учитывая капризы качества адресных данных, эти аргументы не убедительны.

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

Аналогичным образом, управление территорией лучше всего проводить на более узком уровне, чем муниципалитет. Некоторые муниципалитеты огромны, а имена могут быть неоднозначными. Вместо этого используйте почтовый индекс или ZIP + 4 (в зависимости от юрисдикции). Это гораздо более гранулированный и недвусмысленный характер. Опять же, инструмент качества данных адреса гарантирует, что у вас есть надлежащее почтовое кодирование на ваших адресах.

Ответ 2

По моему опыту, да.

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

2 Вы можете заполнить их и подтвердить их из внешних открытых источников или органов стандартизации. Например, для стран это международный стандарт ISO3166

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

4 Если вы когда-нибудь заходите на многоязычный язык, у вас уже будут имена для перевода всего в одном месте.

5 Если вы когда-либо обмениваетесь или связываете данные с другими сторонами или приложениями, вам понадобятся общие классификации

Ответ 3

Прежде чем я начну, хочу отметить, что {город, штат, страна} не является адресом.

Если такие вещи, как Город, государство или Страна, будут нормализованы и сломаны в свою таблицу, а затем в этой таблице есть CityId и StateId колонны. Мы обсуждали, было ли это хорошо или плохо решение.

Нормализация хорошая. Я почти всегда выступаю за нормализацию.

Но использование идентификационных номеров вместо текста не имеет ничего общего с нормализацией. Подстановка "CityId" для "City" и "StateId" для "State" не влияет на обычную форму таблицы. Если бы это было в 3NF до этого изменения, оно все равно будет в 3NF после этого изменения.

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

Самый простой способ повысить целостность данных для городов - это выбрать отдельные города в новую таблицу. (Синтаксис PostgreSQL.)

select distinct city, state, country
into new_table
from person;

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

alter table new_table
add primary key (city, state, country);

Теперь вы можете объявить ограничение внешнего ключа, чтобы гарантировать, что {city, state, country} всегда будет ссылаться на одну строку в этой новой таблице.

alter table Person
add constraint city_state_country_from_new_table
foreign key (city, state, country)
references new_table (city, state, country)
on update cascade;

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

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

select distinct state, country
into another_new_table
from new_table;
etc., etc.

Сказав все это, добавление суррогатного ключа в new_table является оправданным дизайнерским решением, но только если вы потратите некоторое время на размышления об этом. (Не думаю, что это никогда не оправдано.)

Наиболее непосредственным эффектом замены {city, state, country} суррогатным ключом является то, что теперь вам нужно объединение в каждом запросе таблицы, для которой раньше не было объединений. Вы можете проверить влияние на производительность со случайными данными выборки. Пока у вас много миллионов строк, вы, вероятно, обнаружите, что естественный ключ быстрее, чем соединение на суррогатном ключе. Это то, что я нашел, когда тестировал.

Ответ 4

Да, почти наверняка. Если страна или город меняет имя, вы меняете его в одном месте, и все ссылки автоматически обновляются.

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

Наконец, если вам нужен список стран (например, для заполнения списка), у вас есть одно место для ссылки. (В противном случае вы в конечном итоге сделаете SELECT DISTINCT из своей таблицы персонажей, что сомнительно.)

Ответ 5

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

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

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

Ответ 6

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

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

Ответ 7

Это зависит от того, откуда вы получаете данные для города, штата и страны.

Если ваше приложение позволяет пользователю вводить эту информацию, но заставляет их выбирать эти значения из раскрывающихся списков, которые заполняются с использованием ваших основных данных, тогда будет хорошей идеей свернуть эти три поля на нечто вроде "locationId" и иметь таблицу, в которой хранятся записи (city_id, state_id, country_id). Вам не нужны эти три идентификатора в таблице Person, так как комбинация будет очень редко меняться.

Наоборот, если вы разрешаете своим пользователям вводить значения для Города, Штата и Страны, тогда может оказаться сложным разделить их на отдельную таблицу из-за разных значений для одного и того же города/штата/страна.

Ответ 8

Я думаю, что уровень нормализации действительно зависит от того, насколько велика будет приложение. Как минимум, у меня была бы хотя бы таблица адресов, чтобы CRUD можно было выполнять по адресам, не привязанным к пользователям. Вероятно, вы захотите разбить его, если в пользовательском интерфейсе есть планы по выпадающим спискам, которые перечисляют города или штаты или предоставляют веб-службы. Это становится немного сложнее, если вам нужно учитывать внешние адреса и APO/FPO. цели нормализации, перечисленные на странице wikipedia, могут стоить того, чтобы посмотреть, следует ли учитывать какой-либо из сценариев в вашем проекте. Приложите все усилия, чтобы не дублировать данные или усилия без предварительного проектирования.

Я хотел бы предоставить дополнительную информацию, которую может рассмотреть ваша команда:

У Luke W. есть отличная информация о разработке пользовательского интерфейса для адресов.

• http://www.lukew.com/ff/entry.asp?605
• http://www.lukew.com/ff/entry.asp?968

Если вы развертываете через Интернет, существует множество API-интерфейсов веб-сервисов, которые уже управляют данными о местоположении.

• https://www.usps.com/business/web-tools-apis/list-of-apis.htm
• http://www.zippopotam.us/

Если данные необходимо поддерживать внутренне или вам не нравится полагаться на внешние службы, используйте один из открытых данных, таких как GeoNames. Данные представляют собой текстовый файл, разделенный вкладкой, но могут быть легко проанализированы с помощью script для автоматической загрузки данных.

Ответ 9

Проблема с {страной, состоянием, городом} заключается в том, что они являются ключом-кандидатом для ссылочной таблицы. В SQL, {страна, штат, город} не может быть ключом кандидата (или даже первичным ключом), , если состояние (или страна) может отсутствовать или NULL. (этого можно избежать, разрешив для них пустую строку, которая отличается от NULL, но это будет уродливый взлом, IMO). То же самое можно было бы использовать для zip-кода, который можно было бы сделать только в ключе кандидата, добавив country к нему. И оба могут отсутствовать, неизвестно или NULL.

Единственный способ обойти уязвимые ключи-кандидаты - понизить их до (не уникальных) индексов и добавить суррогатный первичный ключ, например, в:

CREATE TABLE cities
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_name varchar -- you _could_ squeeze this out into a separate "countries" table
    , state_name varchar   -- you could even squeeze this out, but it would need a composite FK
    , city_name varchar NOT NULL
    );

CREATE TABLE adresses
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
    , city_id INTEGER references cities(city_id) -- could be NOT NULL
    );

WRT {city,state}: вы могли бы выжать их в таблицу соединений (это в основном проблема BCNF, возможно, даже проблема 4NF, если все поля объединения не были NULLABLE), как в:

    --
    -- Plan B:
    --
CREATE TABLE country2
    ( country_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_name varchar NOT NULL
    , country_iso varchar
    -- ...
    , UNIQUE (country_name)
    );

CREATE TABLE country_state2
    ( cs_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_id INTEGER NOT NULL REFERENCES country2(country_id)
    , state_name varchar
    );
CREATE TABLE cities2
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , cs_id INTEGER REFERENCES country_state2(cs_id)
    , city_name varchar NOT NULL
    );

CREATE TABLE adresses2
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
    , city_id INTEGER references cities2(city_id) -- could be NOT NULL
    );

Если вы действительно должны это сделать, это вопрос вкуса (см. @Joel Brown ответ). Нормиллизация, безусловно, поможет в случае массовых операций переименования, таких как слияние муниципалитетов в OQ. Для небольших наборов адресов (до нескольких тысяч) дополнительная сложность, вероятно, будет стоить дороже, чем она могла бы получить. Эта сложность особенно дорого стоит для интерфейсных приложений, используемых для хранения данных. Для СУБД несколько объединений не будут стоить так дорого (для небольших размеров) и могут даже повысить производительность (для больших размеров). Нормализация неплохая для производительности.

ОБНОВЛЕНИЕ (после комментария Майка Шеррилла по кошкам):

Если бы мы могли налагать ограничения NOT NULL на {country, state, city} (или там ids), мы могли бы также наложить УНИКАЛЬНЫЕ ограничения на (составные) ключи кандидата, в которых они входят:       -       - План C:       -   CREATE TABLE страна3       (country_id INTEGER NOT NULL PRIMARY KEY - может быть серийным...       , country_name varchar NOT NULL       , country_iso varchar       , UNIQUE (страна_имя)       );

CREATE TABLE country_state3
    ( cs_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , country_id INTEGER NOT NULL REFERENCES country3(country_id)
    , state_name varchar NOT NULL
    , UNIQUE (country_id,state_name)
    );
CREATE TABLE cities3
    ( city_id INTEGER NOT NULL PRIMARY KEY -- could be a serial ...
    , cs_id INTEGER NOT NULL REFERENCES country_state3(cs_id)
    , city_name varchar NOT NULL
    , UNIQUE (cs_id,city_name)
    );

CREATE TABLE adresses3
    ( person_id INTEGER NOT NULL PRIMARY KEY -- could be a serial
    , last_name varchar NOT NULL
    , first_first_name varchar
    , gender CHAR(1)
    , dob DATE
            -- allowing NULL here allows for 'embryonic' records without city/state/country info.
    , city_id INTEGER references cities3(city_id)
    );

Хотя это ограничение NOT NULL позволит избежать дубликатов в {city, state, country}, он также будет налагать на NOT NULL, очевидно. Это может быть невозможно или недействительно в других странах (чем Канада или США). В Нидерландах у нас нет state или county; у нас есть provincie, который вряд ли используется (только для устранения неоднозначности, если необходимо). Аналогично для французского departements, IIRC.

Ответ 10

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

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

Это также ускорит время записи записи, если вы индексируете столбец city/state/country, поскольку короткая запись числового индекса намного быстрее, чем полный текстовый индекс.