Всем привет!

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

Сегодня хочу поговорить о средствах связи контрагента.
Итак, рассмотрим коробочную версию данного функционала (хотя как правило, оно достается уже с высокой степенью кастомизации) для Terrasoft XRM 3.2 например (более низкие версии тоже подойдут).
Имеем: средства связи контрагента у нас хранятся в 2-х местах:
1) В таблице контрагента tbl_Account (поля Communication1, Communication2,...,Communication5 и Communication1TypeID, Communication2TypeID,...Communication5TypeID)
2) В Таблице средств связи контрагента tbl_AccountCommunication

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

Минусы ниже:
1) Главный: нам нужно поддерживать целостность данных в 2-х местах: операция Insert/Update/Delete в средствах связи ведет к необходимости выполнить операцию Update в контрагенте и наоборот.
2) Очень часто изменения в таблицах логируются (например, в обеих таблицах). Изменяем 1 телефон в таблице контрагента, вопрос: Сколько записей будет вставлено в таблицы логов? Зависит от реализации механизма поддержки целостности: от 2-х до 6.
3) Бизнес-пользователям захотелось видеть на форме компаний 6 средств связи... или даже 10. Придется добавлять новые поля в таблицу, изменять механизм поддержки целостности, апдейтить таблицу контрагентов (не забыв/забыв о том, что изменения логируются)
4) Часто CRM интегрирована с другими существующими системами. Сколько пакетов интеграции "полетит" при изменении 1 телефона в таблице контрагентов? Аналогично: от 2 до 6.
Возможно, забыл что-то еще. Выяснить все детали того, что происходит можно профайлером на тестовой среде.

Как избавиться от данных проблем: провести нормализацию. Не буду вдаваться в подробности 1,2,3 нормальных форм, нормализации/денормализации, но, очевидно, что средства связи должны храниться в таблице средств связи :smile: (Вопрос о целесообразности tbl_AccountCommunication и tbl_ContactCommunication оставим в стороне).

Что тогда с выводом информации об N средствах связи на форме компании? Есть 2 опции:
1) Заменить вывод информации в соответствующие контролы из таблицы tbl_Account на tbl_AccountCommunication
2) Вывести на форме компании весь грид средств связи и убрать его из деталей.

Я рассмотрю 1 подход.
Нам потребуется внести незначительные изменения в 2 сервиса: wnd_AccountEdit и scr_AccountEdit.

1) Добавляем 5 невизуальных компонентов DatasetLink на форму Контрагента (dlCommunication1, dlCommunication2, ... dlCommunication5) и для каждого из них выбираем датасет ds_AccountCommunication.
Невизуальные компоненты

2) На самой форме меняем в соответствующих контролах DatasetLink на те, что мы создали в первом шаге и выбираем соответствующие поля DatafieldName (тип средства связи CommunicationTypeID и номер Number).
Изменения в контролах
Сохраняем изменения и переходим к обработчику.

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

//Функция подготовки данных для отображения средств связи из таблицы tbl_AccountCommunication
function InitCommunication(position){  
        eval('var CommunicationDataset = dlCommunication'+ position + '.Dataset');//Определяем целевой датасет
        ApplyDatasetFilter(CommunicationDataset, 'AccountID', dlData.Dataset('ID'), true);//Фильтр по контрагенту
        ApplyDatasetFilter(CommunicationDataset, 'Position', position, true);//Фильтр по номеру позиции   
        RefreshDataset(CommunicationDataset);
        if(CommunicationDataset.IsEmpty){//Если средства связи не существует, нужно его добавить и связать с контрагентом и присвоить номер позиции
                CommunicationDataset.Append();
                CommunicationDataset('Position') = position;
                CommunicationDataset('AccountID') = dlData.Dataset('ID');
        }                      
}

В обработчик события OnPrepare добавляем вызов функции.

function wnd_AccountEditOnPrepare(Window) {
        Initialize();
        wnd_BaseDBEditOnPrepare(Window);
/* PRODUCT XRM */
        UpdateAccountBasicCurrencyCaptions();
        InitializePostponementPayment(Self);
/* ENDPRODUCT XRM */
        InitAccountInfo();
//Заполняем датасеты средств связи
        for(var i = 1; i 6; i++)
           InitCommunication(i);
}

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

4) Займемся сохранением данных в tbl_AccountCommunication при сохранении формы контрагента. Нужно добавить обработчик события OnDatasetAfterPost к невизуальному компоненту основного датасета dlData.
Это самый простой вариант: если запись контрагента новая, то при срабатывании этого события она гарантированно сохранена в базе данных, и у нас не получится так, что средства связи окажутся привязаны к несуществующей компании (или если у нас есть внешний ключ из tbl_AccountCommunication на tbl_Account мы получим ошибку от SQL сервера).
Если нам вдруг требуется валидация средств связи перед их сохранением (например у нас нет обработки правильности ввода информации "на лету" еще при заполнении данных на форме), то можем соответствующий функционал добавить в обработчик OnDatasetBeforePost к невизуальному компоненту основного датасета, с возможностью отменить сохранение основной записи контрагента.

function dlDataOnDatasetAfterPost(Dataset) {
        for(var i = 1; i 6; i++)
           CommitCommunication(i);
}

function CommitCommunication(position){
        eval('var CommunicationDataset = dlCommunication'+ position + '.Dataset');
        if(GetFieldsValuesAreChanged(CommunicationDataset, 'Number', 'CommunicationTypeID'))   
                CommunicationDataset.Post();
}

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

Можно сохранять изменения и проверять работу.

Замечания.
Поскольку это "учебный" пример, для его доработки до уровня продакшена нужно будет самостоятельно посмотреть следующие моменты:
1) Стоит сохранить число средств связи на форме в глобальной переменной AccountEdit в одном месте (чтобы не приходилось искать каждый раз где нужно изменить его если число контролов на форме потом увеличится/уменьшится), и соответственно исправить код вызова функций.
2) Нужно будет найти и отключить функционал поддержки целостности при изменении записи в детали Средства связи (чтобы запись контрагента не обновлялась) и, на всякий случай, такой же функционал у контрагента.
3) Возможно у кого-то возникнет вопрос, что если раньше при открытии формы контрагента шел 1 запрос на выборку из tbl_Account а теперь 1 + 5 запросов из tbl_AccountCommunication и в чем тогда преимущество? Преимущество в том, что мы избавляемся от 4-х серьезных минусов, которые я описал в самом начале, но да, получаем увеличение числа запросов на выборку данных при открытии формы контрагента, но эти запросы очень "легкие" и вряд ли скажутся на производительности (+ мы всегда можем ее увеличить "допиливанием" индексов).
4) Потребуется доработка функционала для отображения телефонов в гриде контрагентов.

Вроде все. Надеюсь, получилось не слишком сложно о простом :smile:
Будут какие-то мысли, комментарии, вопросы, предложения - welcome!

Нравится

Поделиться

2 комментария

"komgbu" написал:3) Возможно у кого-то возникнет вопрос, что если раньше при открытии формы контрагента шел 1 запрос на выборку из tbl_Account а теперь 1 + 5 запросов из tbl_AccountCommunication и в чем тогда преимущество?

А если вытягивать эти все 4 средства связи всё в том же sq_Account, используя LEFT JOIN или в колонках подзапросов?
Хранить в обычных текстовых и лукапных полях без привязки к БД, на AfterPost делать проверку на изменение значения, а затем INSERT или UPDATE в таблицу средств связи контрагента программно.
Будет ли при этом некоторый выигрыш в производительности?

"Зверев Александр" написал:А если вытягивать эти все 4 средства связи всё в том же sq_Account, используя LEFT JOIN или в колонках подзапросов?

Спасибо, Александр, я совсем забыл про этот вариант...
Это хороший вариант - причем у него есть плюсы по сравнению с тем, который я привел в своем примере:
- не нужно трогать стандартные обработчики событий и форму контрагента
"Зверев Александр" написал:Хранить в обычных текстовых и лукапных полях без привязки к БД, на AfterPost делать проверку на изменение значения, а затем INSERT или UPDATE в таблицу средств связи контрагента программно.

Вот этого делать не придется - контролы на форме остаются абсолютно те же самые, никаких дополнительных изменений не нужно. При сохранении датасета ядро (или не ядро - не важно :smile:) само определяет что поля не относятся к таблице tbl_Account и все сохраняется корректно.
- не нужно беспокоиться об отображении средств связи в гриде контрагентов

Я попробовал сделать через соединение:
1) Добавляем 5 раз таблицы tbl_AccountCommunication (с алиасами tbl_AccountCommunication1, tbl_AccountCommunication2,...) связываем их с tbl_Account по AccountID используя LEFT JOIN
Соединение таблиц
2) Удаляем существующие 5 соединений с tbl_CommunicationType и создаем новые с аналогичными именами, но связанные с соответствующими таблицами средств связи
Соединение таблиц
3) Создаем 5 параметров целого типа с цифрами 1...5 (Int1, Int2,...Int5)
Параметры
4) Добавляем условия в WHERE (причем все условия оставляем включенными) в них пишем что tbl_AccountCommunication1.Position = Int1
tbl_AccountCommunication2.Position = Int2
...
Условия
5) В полях запроса Communication1, Communication1TypeID ...Communication5, Communication5TypeID меняем таблицы с tbl_Account на tbl_AccountCommunication1,... tbl_AccountCommunication5 и поля соответственно на Number
Поля
6) Проверяем что все хорошо с полями CommunicationType1Name,...CommunicationType5Name (там не придется ничего менять, если сохранили оригинальное наименование алиасов таблиц)
7) Из таблицы tbl_Account удаляем сначала индексы, затем внешние ключи а затем и поля, относящиеся к средствам связи (ведь главная цель - хранить информацию в одном месте). (На продакшене это делать не обязательно, особенно если нет уверенности в том, что данные в tbl_Account соответствуют данным в tbl_AccountCommunication :smile:)
Таблица контрагентов
Все готово, можно запускать клиент и проверять, что все работает.

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

Мое мнение - и тот и другой вариант не будут сильно "перегружать" SQL сервер запросами.
Поверьте, не это "убивает" производительность системы, а те причины, по которым стоит переделать данный функционал :smile:

Показать все комментарии

Коротко о важном.

Нормализация БД

Первая нормальная форма (1НФ)

  • отсутствуют повторяющиеся группы данных
  • гарантируется элементарности(atomicity) данных (все данные являются автономными и независимыми).

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

Вторая нормальная форма (2НФ)

  • таблица удовлетворяет условиям 1НФ
  • каждый столбец зависит от всего ключа, а не от его части.

Третья нормальная форма (3НФ)

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

 

Другие нормальные формы, не имеющие особой практической ценности:

Нормальная форма Бойса-Кодда(Boyce-Codd)

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

Четвертая нормальная форма

Предназначена для решения вопроса с многозначными зависимостями. Такие ситуации возникают, если в приведенной к 3НФ таблице один столбец составного первичного ключа зависит от другого столбца первичного ключа.

Пятая нормальная форма

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

Шестая нормальная форма (нормальная форма доменного ключа)

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

 

Отношения.

Однажды я услышал от женщин, что мужчины
немедленно стараются покинуть помещение, в котором
прозвучало слово "отношения". ...> ключом к успеху
отношений является осведомленность каждого о своей роли
в данном отношении, а также о правилах и ограничениях,
налагаемых данным отношением.
(С)Robert Viera, “Professional SQL Server 2000 Programming”

 

Типы отношений

  • Одного–к–одному (иммет смысл, когда в разных базах нужно хранить совпадающие данные или когда происходит превышение максимального размера данных строки)
  • Нуля– или одного–к–одному
  • Одного–ко–многим
  • Одного к –нулю, –одному или –многим
  • Многих–ко–многим (развязочные таблицы)

 

Объединения

INNER JOIN

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

LEFT|RIGHT [OUTER] JOIN

Включающее объединение (inclusive join). В результат выборки попадают записи из таблицы, стоящей слева/справа от JOIN соответственно. При этом данные из недостающей «парной» записи будут заполнены NULL.
FROM left_table LEFT JOIN right_table – включены все записи из левой таблицы left_table
FROM left_table RIGHT JOIN right_table – включены все записи из правой таблицы right_table

FULL JOIN

Включающее объединение (inclusive join). В результат выборки попадают не только записи, которые имеют соответствие в другой таблице, но и записи из обоих таблиц, для которых в соответствие в другой таблице не найдено. При этом данные из недостающей «парной» записи будут заполнены NULL.

CROSS JOIN

Перекрестное объединение (декартово произведение). Каждая запись из одной таблицы ставится в соответствие каждой записи из другой таблицы. Количество результирующих записей равно произведению количества записей в обоих таблицах.

Принципы упорядочивания нескольких JOIN’ов

В случае, если необходимо произвести объединение нескольких таблиц, нужно помнить о двух принципах:

  1. Все объединения левее JOIN воспринимаются как одиночная таблица для включения или исключения из запроса.
  2. Все объединения ПРАВЕЕ JOIN ТАКЖЕ воспринимаются как одиночная таблица для включения или исключения из запроса.

Следствием из этих принципов является следующая рекомендация для формирования сложных объединений:

  • Везде, где только можно, следует использовать INNER JOIN.
  • Если возникает необходимость использования OUTER JOIN – их нужно ставить последними, а в начале объединения размещаются INNER JOIN.

    P.S. Все вышеизложенное является общими "постулатами" теории реляционных баз данных, не привязанными к особенностям определенных СУБД.

    Нравится

    Поделиться

    10 комментариев

    Маша! Спасибо огромное! :)) Вспомнила универ и прослезилась...

    "Волжева Мария" написал:FROM left_table RIGHT JOIN right_table – включены все записи из левой таблицы right_table

    Кажется мне, что здесь закралась маленькая неточность.

    Ozzy получает особую благодарность за внимательность :)

    P.S. Исправила.

    Продолжаем уточнения :smile:

    "Волжева Мария" написал:

    Если возникает необходимость использования OUTER JOIN – их нужно ставить последними, а в начале объединения размещаются INNER JOIN.

    Это не совсем корректное утверждение.
    Дело в том, что в каждой СУБД собственный оптимизатор запросов и он сам решает как более правильно (на взгляд оптимизатора) построить запрос, т.е. последовательность объединений и индексов. Как правило оптимизатор строит свои планы выполнения на собранной статистике БД. Если статистика не актуальна, то и план выполнения (а следовательно и сам запрос) будут мягко говоря "тормознутыми".

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

    Евгений, спасибо за замечание.

    Но данная заметка не является попыткой рассмотреть все возможные варианты отработки запросов во всех возможных СУБД.

    Это есть общая рекомендация, и на уровне общей рекомендации она корректна.

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

    Да, и еще. Это просто мое мнение.
    Можно ли изложить статью более доступным языком с примерами. Мне кажется что часть людей на комьюнити не совсем понимают о чем именно идет речь, а тем более как это всё применимо к Террасофт CRM.

    Евгений, мне кажется, вы не понимаете цели этой заметки.

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

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

    P.S. Ваше замечание о том, что "последовательностью юнионов ни на что не влияет", (с возможными оговорками) может быть признано верным отнюдь не для всех версий и типов СУБД.
    С учетом этого рекомендация о порядке джоинов может считаться "хорошей практикой", особенно когда идет речь о работе с базами данных в целом. Не говоря уже о том, что в таком случае код будет нагляднее.

    Вы всё правильно говорите, только вот с Вашим PS мне тяжело согласиться. :smile:
    Т.к. комьюнити посвящено продуктам компании Террасофт, то и речь идет (наверно) о поддерживаемых СУБД: MS SQL, Oracle, Firebird. Про Firebird не скажу когда именно началась поддержка инструкций оптимизатора, а вот MS SQL 2000 уже их поддерживал и Oracle 8 тоже, а даты релизов этих продуктов 1999 и 1997 гг соответственно и их тяжело назвать относительно новыми поколениями.

    Если не ошибаюсь, то мы не декларировали поддержку Oracle 8. На момент выхода первой Terrasoft 3.0.0.x вробе бы была актуальна Oracle 9i.

    Про Oracle 8 я написал как пример когда появилась поддержка хинтов.

    Показать все комментарии

    В предыдущем посте я упомянул термин "нормализация" задач. Хочу пояснить, что имел ввиду и зачем.

    Позвольте, в начале, уточнить определение технологичного процесса.

    1. Это воспроизводимая последовательность задач, выполнение которых приводит к нужному результату.
    2. Данная последовательность представляет собой инструкцию к действиям и описывает кратчайшие пути достижения результата в наиболее вероятных вариациях процесса.
    3. Сотрудник является исполнителем процесса.
    4. Каждая задача нормализована (определение см. ниже).
    5. Процесс включает в себя схемы контроля и регуляции.

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

    1. Это воспроизводимая последовательность результатов, достижение которых приводит к итоговому результату.
    2. Данная последовательность включает в себя рекомендацию к действиям и описывает лучшие практики достижения результата.
    3. Сотрудник является интерпретатором процесса.
    4. Поскольку задачи не нормализованы, процесс включает в себя механизмы самообучения, взаимного контроля и саморегуляции.
    5. Внешний контроль осуществляется по результатам.

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

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

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

    «Нормальная» задача:

    1. Конкретна (понятно что и как необходимо сделать)
    2. Имеет одного ответственного
    3. Нормативами ограниченна во времени
    4. Имеет четкие (однозначные) критерии оценки
    5. Предполагает определенную форму передачи результатов
    6. Подразумевает некие требования к компетенциям исполнителя

    «Нормальный» результат:

    1. Может не иметь одного ответственного (?)
    2. Подразумевает некие требования к компетенциям исполнителя
    3. Ограничен во времени внешними условиями
    4. Критерием оценки является факт достижения результата
    5. Форма передачи результата определяется в каждом конкретном случае
    6. Содержание работы, количество задач является «черным ящиком»

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

    Нравится

    Поделиться

    0 комментариев
    Показать все комментарии