Блог

Хорошо, итак, моя тема не очень описательна, но вот сценарий:

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

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

Надеюсь, это достаточно хорошо объясняет бизнес-пример … перейдем к части SQL! Итак, для решения этой проблемы у нас есть таблицы, которые выглядят следующим образом:

 CREATE TABLE [dbo].[SomeUserEnteredData](
    [Id] [uniqueidentifier] NOT NULL,
    [LastOrOrganizationName] [nvarchar](350) NOT NULL,  // data as entered by end-user
    [FirstName] [nvarchar](50) NULL, // data as entered by end-user
    [FullName]  AS ([dbo].[FullNameValue]([FirstName],[LastName])) PERSISTED,  // data as entered by end-user
    [MappedName]  AS ([dbo].[MappedNameValue]([FirstName],[LastName])))  // this is the 'override' data from the agency

CREATE TABLE [dbo].[CorrectionsByAgency](
    [Id] [uniqueidentifier] NOT NULL,
    [ReplaceName] [nvarchar](400) NOT NULL,
    [KeepName] [nvarchar](400) NOT NULL)

CREATE FUNCTION [dbo].[FullNameValue]
(
    @FirstName as NVARCHAR(40),
    @LastOrOrganizationName as NVARCHAR(350)
)
RETURNS NVARCHAR(400)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @result NVARCHAR(400)
    IF @FirstName = '' OR @FirstName is NULL
        SET @result = @LastOrOrganizationName
    ELSE 
        SET @result = @LastOrOrganizationName   ', '   @FirstName
    RETURN @result
END

CREATE FUNCTION [dbo].[MappedNameValue]
(
    @FirstName as NVARCHAR(50),
    @LastOrOrganizationName as NVARCHAR(350)
)
RETURNS NVARCHAR(400)
AS
BEGIN
    DECLARE @result NVARCHAR(400)
    DECLARE @FullName NVARCHAR(400)
    SET @FullName = dbo.FullNameValue(@FirstName, @LastOrOrganizationName)
    SELECT top 1 @result = KeepName from CorrectionsByAgency where ReplaceName = @FullName
    if @result is null
        SET @result = @FullName
    RETURN @result
END
  

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

С точки зрения производительности сервера это решение ВОНЯЕТ. Вычисляемый SomeUserEnteredData.MappedName столбец не может быть проиндексирован, и никакое представление, считывающее данные из этого столбца, также не может быть проиндексировано! Это никак не может работать для наших нужд, если мы не можем индексировать значения mappedName.

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

Есть ли у кого-нибудь из вас, SQL-гениев, отличные идеи о том, как решить эту проблему?

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

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

 create table dbo.UserEnteredData (
  DocumentId uniqueidentifier not null primary key,
  UserEnteredName nvarchar(1000) not null,
  CorrectedNameId uniqueidentifier null,
  constraint FK_CorrectedNames foreign key (CorrectedNameId)
    references dbo.CorrectedNames (CorrectedNameId)
)

create table dbo.CorrectedNames (
  CorrectedNameId uniqueidentifier not null primary key,
  CorrectedName nvarchar(1000) not null
)
  

Теперь вам нужно убедиться, что логика вашего приложения может выполнять что-то вроде этого:

1. Внешний пользователь вводит грязные данные
2. Пользователь агентства просматривает грязные данные и идентифицирует как неправильное, так и исправленное имя
3. Приложение проверяет, существует ли исправленное имя
4. Если нет, создайте новую строку в dbo.Исправленные имена
5. Создайте новую строку в dbo.UserEnteredData, с CorrectedNameId

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

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