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

Мне интересно, хороший ли это дизайн. У меня есть несколько таблиц, в которых требуется адресная информация (например, улица, почтовый индекс / почтовый индекс, страна, факс, электронная почта). Иногда один и тот же адрес повторяется несколько раз. Например, адрес может быть сохранен для поставщика, а затем для каждого отправленного ему заказа на покупку. После этого поставщик может изменить свой адрес, и все последующие заказы на поставку должны иметь новый адрес. Это сложнее, но это пример требования.

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

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

Я склоняюсь к варианту 2. Есть ли способ лучше?

РЕДАКТИРОВАТЬ: я должен сохранить адрес в заказе на покупку в том виде, в котором он был отправлен. Кроме того, это немного сложнее, чем я предлагал, поскольку могут быть адрес доставки и адрес для выставления счетов (есть также множество других таблиц, в которых есть информация об адресе).

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


sql database-design rdbms
person WW.    schedule 20.11.2008    source источник

Ответы (7)


arrow_upward
27
arrow_downward

Я использую это как один из вопросов на собеседовании. Следующее - хорошее место для начала:

Addresses
---------
AddressId (PK)
Street1
... (etc)

и

AddressTypes
------------
AddressTypeId
AddressTypeName

и

UserAddresses (substitute "Company", "Account", whatever for Users)
-------------
UserId
AddressTypeId
AddressId

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

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

person Eric Z Beard    schedule 20.11.2008
comment
Хорошее решение; Раньше я сам использовал подобные вещи. Это работает хорошо. На самом деле, я часто использовал более общую реализацию с использованием Contact и ContactType, включающих адрес; это обрабатывает пользователей без физического адреса, но у которых есть электронная почта и т. д. - person Paul Sonier; 21.11.2008
comment
У меня есть только адрес доставки и адрес получателя счета, поэтому я подумал: столбцы PO.DeliveryAddressID и PO.BillToAddressID. Маловероятно, что будут добавлены какие-либо другие типы, поэтому я думаю, что моделирование «многие-ко-многим» - это излишне. - person WW.; 21.11.2008
comment
Вопрос: Что, если бы у пользователя / клиента / компании было более одного адреса? - например, адрес для выставления счета и адрес доставки. Как насчет того, чтобы с такой структурой сохранить два типа адресов против одной идеи пользователя? - person Mohamad; 26.08.2010
comment
Не могли бы вы уточнить, возможно, приведя одну или две строки в качестве примера в таблице AddressTypes? Я только что прочитал книгу ресурсов по модели данных и отчаянно ищу более легкое решение. - person Alix Axel; 07.10.2011
comment
AddressTypes будет содержать всего несколько строк. 1 = Дом, 2 = Бизнес, 3 = Доставка и т. Д. - person Eric Z Beard; 07.10.2011
comment
в чем преимущество наличия адреса в отдельной таблице? Вы действительно планируете сканировать всю таблицу, чтобы увидеть, существует ли уже адрес (и, таким образом, связать его с этим идентификатором, который уже существует), прежде чем подключать его к пользователю? - person Don Cheadle; 13.09.2015

arrow_upward
6
arrow_downward

Вариант 2, без сомнения.

Следует помнить о некоторых важных вещах: это важный аспект дизайна - указывать пользователям, когда адреса связаны друг с другом. Т.е. корпоративный адрес совпадает с адресом доставки; если они хотят изменить адрес доставки, хотят ли они также изменить корпоративный адрес или они хотят указать новую погрузочную платформу? Подобные вещи, а также возможность предоставлять пользователям эту информацию и изменять вещи с такой степенью детализации ОЧЕНЬ важны. Это тоже важно для обновлений; дать пользователю возможность «разбивать» записи. Не то чтобы пользовательский интерфейс такого типа легко спроектировать; на самом деле это сука. Но это действительно важно сделать; что-то меньшее почти наверняка вызовет у ваших пользователей сильное разочарование и раздражение.

Также; Я настоятельно рекомендую хранить старые адресные данные; не запускайте процесс для его очистки. Если у вас нет ОЧЕНЬ загруженной базы данных, ваше программное обеспечение базы данных сможет обрабатывать избыточные данные. Действительно. Одна распространенная ошибка, которую я вижу в отношении баз данных, - это попытка переоптимизации; вы ДЕЙСТВИТЕЛЬНО хотите оптимизировать свои запросы, но НЕ хотите оптимизировать неиспользуемые данные. (Опять же, если активность вашей базы данных ОЧЕНЬ ВЫСОКАЯ, вам может потребоваться что-то для этого, но почти наверняка ваша база данных будет работать хорошо, если в таблицах все еще есть избыточные данные.) В большинстве ситуаций это на самом деле более выгодно. просто позволить вашей базе данных расти, чем пытаться ее оптимизировать. (Удаление случайных данных из ваших таблиц не приведет к значительному уменьшению размера вашей базы данных, а когда это произойдет ... ну, вызванная этим переиндексация может привести к огромной утечке данных из базы данных.)

person Paul Sonier    schedule 20.11.2008
comment
Когда адрес уже используется, не редактируйте его ни по какой причине. Если вам нужно что-то изменить на новый адрес, посмотрите, существует ли этот новый адрес (и используйте его), или вставьте новый. Затем попытайтесь удалить старый адрес, но не суетитесь (без ошибок), если он не может быть удален, потому что он все еще используется. - person Jonathan Leffler; 21.11.2008

arrow_upward
2
arrow_downward

Думаю, я согласен с JohnFx ..

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

Лучшими полями будут:

  • Имя / Компания
  • Адрес (многострочное текстовое поле)
  • Страна

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

person Stein G. Strindhaug    schedule 20.11.2008

arrow_upward
1
arrow_downward

Вы хотите вести исторический учет того, какой адрес изначально был указан в заказе на покупку?

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

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

person JohnFx    schedule 20.11.2008
comment
Хотя я согласен с тем, что необходимость в cron для очистки ваших данных - ОЧЕНЬ плохой знак, вариант 2 - правильный способ сделать это. Вариант 2 - нормализованное решение. как отмечает cagcowboy, на самом деле не должно быть необходимости в работе cron. - person rmeador; 21.11.2008
comment
На мой взгляд, это чрезмерно нормализовано и вызовет больше головной боли, чем того стоит. Вариант 1 подходит, если адреса описывают сущности в таблицах, в которые они добавляются, при условии, что они не дублируются чрезмерно. - person JohnFx; 21.11.2008

arrow_upward
1
arrow_downward

Почему какая-либо из строк в адресной таблице перестала использоваться? Конечно, на них по-прежнему будет указывать заказ на покупку, в котором они использовались?

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

person cagcowboy    schedule 20.11.2008
comment
В конце концов я удалю старые заказы на покупку, поэтому в какой-то момент адрес больше не будет использоваться ничем. - person WW.; 21.11.2008
comment
Если все ссылки на адреса являются внешними ключами (так что любая таблица, которая ссылается на таблицу адресов, имеет формальный внешний ключ), то триггер удаления или триггер обновления идентификатора адреса может попытаться удалить старый идентификатор адреса в таблице. таблица адресов. Если это удастся, ссылок не останется. - person Jonathan Leffler; 21.11.2008
comment
Продолжение: если запускаемое удаление завершается неудачно, это означает, что другая строка все еще ссылается на строку адреса (возможно, могут быть другие ошибки), и до тех пор, пока СУБД не мешает вам игнорировать эту ошибку (чего не должно) , затем вы можете продолжить удаление или обновление ссылки. - person Jonathan Leffler; 21.11.2008
comment
@Joanthan Leffler: Согласитесь, что ваш подход к триггеру технически верен, но я думаю, что для повышения производительности лучше удалять записи адресов в фоновом режиме. В моей текущей модели есть 10 дочерних таблиц адресов. - person WW.; 21.11.2008

arrow_upward
0
arrow_downward

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

Таблица адресов - хорошая идея. Сделайте для него уникальное ограничение, чтобы один и тот же объект не мог иметь повторяющиеся адреса. Вы все равно можете получить их, поскольку пользователи могут добавить еще один, вместо того, чтобы искать их, и если они пишут несколько иначе (St. вместо Street), уникальное ограничение не помешает этому. Скопируйте данные во время создания заказа в заказ. Это тот случай, когда вам нужно несколько записей, потому что вам нужна историческая запись того, что вы куда отправляли. Для меня не имеет смысла разрешать только вставки и удаления в таблице, поскольку они не безопаснее обновлений и требуют больше работы для базы данных. Обновление выполняется за один вызов базы данных. Если в вашей идее адрес меняется, вы должны сначала удалить старый адрес, а затем вставить новый. Не только больше обращений к базе данных, но и вдвое больше шансов сделать ошибку кода.

person HLGEM    schedule 20.11.2008
comment
Один и тот же объект может иметь более одного адреса. У них может быть адрес, на который они отправили прошлогодние заказы на закупку, и другой адрес из этого года и т. Д. Предлагаемая мной таблица адресов вообще не будет хранить детали объекта. - person WW.; 21.11.2008
comment
Я имел в виду дубликаты одного и того же адреса, но не более одного адреса на объект. - person HLGEM; 21.11.2008

arrow_upward
0
arrow_downward

Я видел, как каждая система, использующая вариант 1, испытывала проблемы с качеством данных. Через 5 лет 30% всех адресов перестанут быть актуальными.

person Stephan Eggermont    schedule 03.02.2009