У меня есть таблица под названием Bookings. Эта таблица содержит данные, представляющие бронирование, сделанное для конкретной услуги, со многими переменными.

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

Мое решение в то время состояло в том, чтобы создать таблицу изменений, которая отслеживала бы любые изменения, внесенные в бронирование. Затем, всякий раз, когда модели бронирования предлагалось вернуть бронирование, она добавляла внесенные изменения (в afterFind() обратном вызове Cake) и представляла самую последнюю версию бронирования, что-то вроде этого (извините за рисование):

Этот метод отлично работает, когда вы просите модель бронирования вернуть бронирование # 1234. Он возвращает самое актуальное представление бронирования, включая все изменения (наложенные друг на друга), включая массив, содержащий все изменения и исходные данные бронирования для справки.

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

В этом примере модель смотрит прямо на исходные данные для строк, где abc они синие, и не возвращает этот результат, потому что значение blue находится в модификации, которая прикрепляется после нахождения исходных результатов.

Что я сделал сейчас, так это поместил запрос в beforeFind() обратный вызов модели бронирования для поиска изменений, соответствующих заданным критериям, присоединившись к бронированию, чтобы убедиться, что любые другие критерии по-прежнему совпадают. Когда он возвращает синий цвет в примере выше, он сохраняет этот результат в массиве как свойство класса и продолжает с обычным find() , но исключает возврат идентификатора этого бронирования (потому что мы нашли более актуальную версию этого). Затем он объединит их вместе, отсортирует их снова и т. Д. В afterFind() .

Это работает, хотя и немного более затянуто, чем я надеялся.

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

Я думал, что мне нужно удалить соединение вручную и вместо этого создать ассоциацию модели. Будет beforeFind() ли и afterFind() модели бронирования по-прежнему выполняться, когда я запрашиваю, скажем, модель клиента, у которой много бронирований (чтобы применить изменения к каждому бронированию)?

Другим моим вариантом было вернуть из MySQL больше строк, чем необходимо, удалив любые критерии, которые могут содержаться в изменениях, а затем использовать PHP для фильтрации результатов в соответствии с моими критериями поиска. Этот вариант меня немного напугал, потому что набор результатов может быть огромным без этого критерия…

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

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

 $get_blue = $this->Booking->find('all', array(
    'conditions' => array(
        'Booking.abc' => 'blue'
    )
));
 

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

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

 $get_transactions_on_blue_bookings = $this->Transaction->find('all', array(
    'joins' => array(
        array(
            'table' => 'sql_bookings_table', // non-standard Cake format, I know - it's an example
            'alias' => 'Booking',
            'type' => 'LEFT',
            'conditions' => 'Booking.booking_id = Transaction.booking_id'
        )
    ),
    'conditions' => array(
        'Booking.abc' => 'blue'
    )
));
 

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

Любая помощь по этому вопросу была бы весьма признательна.

Редактировать

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

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

Похоже, вы пытаетесь реализовать временную базу данных. Временная поддержка была одним из основных дополнений к стандарту ANSI / ISO SQL: 2011. MySQL (как и большинство СУБД) отстает от стандарта. Думайте о временной базе данных как о СУБД, эквивалентной CVS / SVN / Git.

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

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

• Подход с одной таблицей.Когда вам нужно внести изменения, вы делаете UPDATEs это в своих исходных записях, и если у вас нет какой-либо доморощенной логики запуска / аудита, журнал истории отсутствует. Даже если у вас есть журнал аудита / изменений, вам придется немного покопаться, чтобы восстановить историю изменений.
• Подход с двумя таблицами.Вместо внесения изменений на месте, вы разбиваете свои данные на две таблицы, одну с базовыми / исходными записями (например, booking), а другую таблицу для ваших изменений / модификаций / дельт. Тогда, по крайней мере, у вас будут сохранены исходные данные, но опять же вам придется написать сложную логику для просмотра исходных данных с наложенными изменениями. Становится еще хуже, если вы хотите применить только некоторые изменения.
• Подход с предварительно рассчитанной результирующей таблицей. Вы храните 3 или более таблиц: базовые записи, модификации, а также таблицу, которая пытается всегда иметь результирующий результат (обновляет базу модификации). Удачи в написании триггеров и процедур для выполнения этого вычисления, когда бы вы это ни делали INSERTs , и да помогут вам Небеса, если UPDATE DELETE потребуется или. Настройка является хрупкой и может нарушить синхронизацию, например, из-за взаимоблокировок и отката. Если вы не делаете этого в БД с помощью триггеров / процедур, вы можете попытаться реализовать результирующее вычисление в коде приложения, но вам повезет в этом — и это может стать уродливым с многопоточными потребителями. И все же у вас нет легкого доступа к результирующим результатам, если применены только некоторые изменения.

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

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

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

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

Вот блок-схема моего процесса обновления базы данных theroy: http://i1371.photobucket.com/albums/ag300/joshua127/BookingFlowchartinsert_zps5c2d55f8.png

Вот блок-схема моего процесса выбора theroy: http://i1371.photobucket.com/albums/ag300/joshua127/BookingFlowchartselect_zps702fa902.png

Надеюсь, это поможет, просто еще один способ взглянуть на это.

P.S. Чтобы сохранить финансовую информацию неизменной, вы могли бы написать свои функции обновления для подсчета количества столбцов, подлежащих обновлению (на основе вашего массива обновлений имен столбцов), и предоставить переменные для хранения определенных значений только для этих столбцов. вы можете ссылаться на индексы массива ($array[‘index’] ) в инструкции SQL, чтобы сделать ее динамической.

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

Обычно я добиваюсь такого подхода, создавая параллельную таблицу, которая называется как исходная, добавляя _history к ней. Bookings_history в вашем случае. Структура будет аналогична исходной, но с добавлением столбцов:

a) timestamp , которые сохраняются, когда произошло изменение

б) id для идентификации строки в исходной таблице

Будет создан уникальный индекс для этих двух столбцов.

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

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

Я надеюсь, что это поможет.

Из ответов, которые вы уже собрали, совершенно очевидно, что что бы вы ни делали, это потребует некоторого или большего перепроектирования.

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

Вы можете изменить bookings таблицу, чтобы добавить увеличенное целое число для каждого бронирования (т.е. version_number ) и is_latest поле. Таким образом, вы можете запросить с is_latest=true , чтобы получить текущую запись и ее version_number . Если оно равно 0, изменений не было, если оно> 0, то изменения есть (это число будет равно количеству изменений). Вы сможете «перемотать» или «воспроизвести» историю, если перейдете с последней версии на 0 или наоборот, и каждый раз у вас будет полная запись, понятная вашему приложению без изменений.

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

Преимущество этого заключается в том, что, скорее всего, вам потребуется изменить только Booking модель, но это будет зависеть от вашего кода.

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