Как правильно индексировать таблицу, к которой две другие таблицы имеют отношение «один ко многим»?

Представьте, что у меня есть три таблицы: «клиенты», «компании» и «номера телефонов». И клиенты, и компании могут иметь несколько телефонных номеров. Как лучше всего индексировать phone_numbers? Иметь как customer_id, так и company_id и оставить один из них нулевым? Что делать, если существует более двух таблиц с отношением «один ко многим» с phone_numbers?


sql database indexing one-to-many
person zaratustra    schedule 01.03.2010    source источник
comment
Какой сервер базы данных вы используете? MySQL? МС SQL сервер? Другой ? И какой запрос вы будете использовать? Будете ли вы обычно пытаться получить номера телефонов одной компании/клиента?   -  person Pascal MARTIN    schedule 01.03.2010

Ответы (4)


arrow_upward
1
arrow_downward

В ваших бизнес-правилах может быть указано только отношение «один ко многим», но на самом деле люди и компании могут быть отношениями «многие ко многим». У одного человека может быть много телефонных номеров (домашний, мобильный и т. д.), и номер телефона может относиться ко многим людям (мне, моей второй половинке и т. д.). Точно так же номер компании и мой рабочий номер могут совпадать — вы просто используете добавочный номер, чтобы связаться со мной напрямую.

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

person OMG Ponies    schedule 01.03.2010
comment
В случае «многие ко многим» вы не могли бы просто проверить, существует ли номер, а затем, если он существует, вернуть значение ключа уже существующего номера телефона? например, у клиента A есть номер, а у компании X такой же номер a, поэтому верните идентификатор a. В то время как обычным поведением, если бы номер компании X был другим, было бы создание новой записи, а затем возврат этого идентификатора. - person msarchet; 01.03.2010
comment
@msarchet: правильно настроенные ограничения сделают то же самое, но нужно будет выполнить только один запрос. Просто нужно изящно обработать сообщение об ошибке. - person OMG Ponies; 01.03.2010

arrow_upward
0
arrow_downward

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

person msarchet    schedule 01.03.2010

arrow_upward
0
arrow_downward

Я бы добавил два столбца в таблицу phone_numbers. Первым будет индекс, который сообщает вам, с какой таблицей ассоциироваться (скажем, 1 = клиенты и 2 = компании). Второй будет внешним ключом к соответствующей таблице.

Таким образом, вы можете добавить столько источников телефонных номеров, сколько захотите.

Если у конкретного человека или компании более одного номера телефона, в таблице phone_numbers будет несколько строк.

person John    schedule 01.03.2010

arrow_upward
0
arrow_downward

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

CREATE TABLE CUSTOMER_XREF_PHONE
( CUSTOMER_ID      NUMBER NOT NULL,
  PHONE_NUMBER_ID  NUMBER NOT NULL,
  CONSTRAINT       CUSTOMER_XREF_PHONE_PK 
    PRIMARY KEY      (CUSTOMER_ID, PHONE_NUMBER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_UK 
    UNIQUE           (PHONE_NUMBER_ID, CUSTOMER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK01
    FOREIGN KEY      (CUSTOMER_ID)
      REFERENCES       CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE,
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK02
    FOREIGN_KEY      (PHONE_NUMBER_ID)
      REFERENCES       PHONE_NUMBERS (PHONE_NUMBER_ID) ON DELETE CASCADE
);

Такой шаблон реализации может:

  • Будьте полностью защищены ограничениями ссылочной целостности на уровне базы данных

  • Поддержка двунаправленного доступа (иногда вам нужно посмотреть, у кого еще есть этот номер телефона)

  • Будьте самоочищающимися, если ваша база данных поддерживает ON DELETE CASCADE

  • Быть расширенным за счет использования атрибута «тип отношения» для сопоставления нескольких независимых отношений между объектами, таких как:

    • customer has a home telephone number
    • у клиента есть дневной номер телефона
    • у клиента есть номер телефона факса
    • у клиента есть номер мобильного телефона
person Adam Musch    schedule 01.03.2010