Проблемы базы данных SQL с дизайном таблицы адресной книги

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

Я могу создать n-записей для каждого типа, который я хочу. Тип означает здесь данные, такие как «электронная почта», «адрес», «телефон» и т. Д.

У меня есть таблица с именем contact_profiles.

Это имеет только два столбца:

id           Primary key
date_created DATETIME

И еще есть таблица с именем contact_attributes. Это немного сложнее:

id       PK
#profile (Foreign key to contact_profiles.id)
type     VARCHAR describing the type of the entry (name, email, phone, fax, website, ...) I should probably change this to a SET later.
value    Text (containing the value for the attribute).

Теперь я могу ссылаться на эти профили, например, из таблицы моего пользователя. Но отсюда я сталкиваюсь с проблемами.

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

Так что сейчас я бы получил что-то вроде

#profile type    value
1        email   name@domain.tld
1        name    Sebastian Hoitz
1        website domain.tld

Но было бы неплохо получить такой результат:

#profile email           name            website
1        name@domain.tld Sebastian Hoitz domain.tld

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

Так вы знаете, есть ли возможность конвертировать это динамически?

Если вам нужно лучшее описание, пожалуйста, дайте мне знать.

15.12.2008 18:08:27
6 ОТВЕТОВ
РЕШЕНИЕ

Вы заново изобрели дизайн базы данных под названием Entity-Attribute-Value . У этого дизайна есть много недостатков, в том числе обнаруженный недостаток: очень сложно воспроизвести результат запроса в обычном формате с одним столбцом на атрибут.

Вот пример того, что вы должны сделать:

SELECT c.id, c.date_created,
 c1.value AS name,
 c2.value AS email,
 c3.value AS phone,
 c4.value AS fax,
 c5.value AS website
FROM contact_profiles c
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'name')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'email')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'phone')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'fax')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'website');

Вы должны добавить еще один LEFT OUTER JOINдля каждого атрибута. Вы должны знать атрибуты во время написания запроса. Вы должны использовать, LEFT OUTER JOINа не INNER JOINпотому, что нет никакого способа сделать атрибут обязательным (эквивалент простого объявления столбца NOT NULL).

Гораздо эффективнее извлекать атрибуты по мере их сохранения, а затем писать код приложения для циклического прохождения набора результатов, создавая объект или ассоциативный массив с записью для каждого атрибута. Вам не нужно знать все атрибуты таким образом, и вам не нужно выполнять соединение n-way.

SELECT * FROM contact_profiles c
  LEFT OUTER JOIN contact_attributes ca ON (c.id = ca.profile);

Вы спросили в комментарии, что делать, если вам нужен этот уровень гибкости, если не использовать дизайн EAV? SQL не является правильным решением, если вам действительно нужна неограниченная гибкость метаданных. Вот несколько альтернатив:

  • Сохраните TEXTBLOB-объект, содержащий все атрибуты, структурированные в формате XML или YAML.
  • Используйте решение для моделирования семантических данных, такое как Sesame , в котором любой объект может иметь динамические атрибуты.
  • Отказаться от баз данных и использовать плоские файлы.

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

4
15.12.2008 18:38:56
Спасибо, это дает имя проблеме! :) Я хотел бы использовать локальный ассоциативный массив, но что если у меня есть список записей, куда я хотел бы добавить контактную информацию? Должен ли я создать временный массив для всех записей списка, которые я собираюсь показать?
Sebastian Hoitz 15.12.2008 18:31:56
Если вам нужно обновить записи, вы должны делать их по одному. Загрузите из базы данных в массив, измените атрибуты, затем сохраните в базе данных. Если вы сделаете это, вам также необходимо отслеживать удаление атрибутов; Вы не можете просто сбросить элемент массива.
Bill Karwin 15.12.2008 18:36:57

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

SELECT cp.ID profile
  ,cp.Name
  ,(SELECT value FROM contact_attributes WHERE type = 'email' and profile = cp.id) email
  ,(SELECT value FROM contact_attributes WHERE type = 'website' and profile = cp.id) website
  ,(SELECT value FROM contact_attributes WHERE type = 'phone' and profile = cp.id) phone
FROM contact_profiles cp

Если вы используете SQL Server, вы также можете посмотреть на PIVOT .

Если вы хотите показать несколько электронных писем, телефонов и т. Д., Учтите, что в каждом профиле их должно быть одинаковое количество, иначе у вас будут пробелы.

Я также выделил бы столбец типа. Создайте таблицу с именем contact_attribute_types«email», «website» и т. Д. Затем вы сохраните contact_attribute_types.idцелочисленное значение в contact_attributesтаблице.

1
15.12.2008 18:20:47
Хорошее замечание, чтобы выделить тип. Я забыл это! Спасибо :)
Sebastian Hoitz 15.12.2008 18:29:12

Вам нужно будет сгенерировать запрос как:

select #profile,
       max(case when type='email' then value end) as email,
       max(case when type='name' then value end) as name,
       max(case when type='website' then value end) as website
from mytable
group by #profile

Однако это покажет только одно значение для каждого типа на #profile. Ваша СУБД может иметь функцию, которую вы можете использовать вместо MAX для объединения всех значений в виде строки, разделенной запятыми, или вы можете написать ее.

Такого рода модель данных обычно лучше избегать по причинам, которые вы уже упомянули!

0
15.12.2008 18:24:06
Но есть ли альтернатива использованию этой модели данных, если вы хотите иметь такую ​​гибкость в данных, которые вы можете вводить?
Sebastian Hoitz 15.12.2008 18:26:28
Решение Тони также предполагает сортировку NULL ниже любого значения, отличного от NULL. Это не так во всех реализациях SQL.
Bill Karwin 15.12.2008 18:28:33
Так, может быть, использовать MIN вместо MAX?
Tony Andrews 17.12.2008 11:37:48

Вы создаете представление для каждого типа контакта

Когда вам нужна вся информация, которую вы извлекаете из всей таблицы, когда вам нужно подмножество определенного типа контакта, вы извлекаете из представления.

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

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

Я что-то пропустил? Это кажется тривиальным?

РЕДАКТИРОВАТЬ:

Я вижу, чего мне не хватало ... Вы пытаетесь быть нормализованными и денормализованными одновременно. Я не уверен, что остальные ваши бизнес-правила для сбора записей. У вас могут быть профили с несколькими или нулевыми значениями для телефона / электронной почты / адресов и т. Д. Я бы оставил ваши данные в том же формате и снова использовал бы sproc для создания нужного вам представления. По мере того, как потребности вашего бизнеса меняются, вы оставляете свои данные в покое и просто создаете другое приложение для доступа к ним.

0
15.12.2008 18:43:36

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

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

Кстати, все архитектурные вопросы о «наилучшей» необходимости требуют такого рода анализа затрат, выгод и рисков.

0
9.05.2009 00:31:07

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

0
17.05.2010 12:29:34