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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если такие вещи, как City, State или Country, должны быть нормализованы и разбиты на отдельную таблицу, и тогда эта таблица будет иметь столбцы 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);

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

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} суррогатным ключом состоит в том, что теперь вам нужно объединение в каждом запросе к таблице, для которой раньше не требовалось никаких объединений. Вы можете проверить влияние на производительность с помощью случайной выборки данных. Пока у вас не будет многих миллионов строк, вы, вероятно, обнаружите, что естественный ключ работает быстрее, чем соединение суррогатного ключа. Это то, что я обнаружил при тестировании.

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

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

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

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

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

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

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

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

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

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

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

Проблема с {country, state, city} заключается в том, что они кажутся потенциальным ключом для указанной таблицы. В SQL {страна, штат, город} не может быть кандидатным ключом (или даже первичным ключом), если штат (или страна) может отсутствовать или иметь значение NULL. (этого можно было бы избежать, разрешив для них пустую строку, которая отличается от NULL, но это было бы уродливым взломом, IMO) То же самое касается почтового индекса, который можно было бы превратить в ключ-кандидат, только добавив к нему 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 ограничений на {страну, штат, город} (или их идентификаторы), мы также могли бы наложить УНИКАЛЬНЫЕ ограничения на (составные) ключи-кандидаты, частью которых они являются: - - План C: - СОЗДАТЬ ТАБЛИЦУ country3 (country_id INTEGER NOT NULL PRIMARY KEY - может быть серийный номер ..., country_name varchar NOT NULL, country_iso varchar, UNIQUE (country_name));

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.

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

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

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

• 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. Данные представляют собой текстовый файл, разделенный табуляцией, но его можно легко проанализировать с помощью сценария для автоматической загрузки данных.

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

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

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