有效管理数据更改 [英] Effective management of data changes

问题描述

我有一个名为Bookings的表.该表包含代表针对特定服务进行的预订的数据，其中包含许多变量.

I have a table called Bookings. This table contains data representing a booking made for a particular service, with many variables.

前段时间，我遇到了当前数据结构的问题，即影响预定时间，日期或价格的任何预订更改都将影响其他关联的财务记录，日期的预订列表等.

A while ago I came across a problem with my current data structure whereby any changes to the booking that affected times, dates or prices would affect other associated financial records, bookings lists for dates etc.

当时我的解决方案是创建一个Modifications表，该表将跟踪对Booking所做的任何更改.然后，每当要求预订模型返回预订时，它都会添加所做的修改(在afterFind() Cake回调中)，并显示预订的最新版本，例如以下内容(例如Paint绘图) ):

My solution at the time was to create a Modifications table which would track any changes made to a Booking. Then, whenever the Booking model was asked to return a booking, it would add on Modifications made (in the afterFind() Cake callback) and present the most up-to-date version of the booking, something like this (excuse the Paint drawing):

当您要求预订"模型返回预订#1234时，此方法可以正常工作.它返回预订的最新表示形式，包括所有修改(彼此层叠)，包括一个包含所有修改和原始预订数据的数组，以供参考.

This method works fine when you ask the Booking model to return booking #1234. It returns the most up-to-date representation of the booking including all modifications (layered on top of each other), including an array containing all the modifications and the original booking data for reference.

我的问题是，我最近意识到我需要能够使用自定义条件查询该模型，并且如果其中一项条件是在修改之一中实现的，结果将是之所以不匹配，是因为该模型正在搜索原始记录，而不是最终显示的记录.我查询模型以返回abc为蓝色(不是灰色)的行的示例:

My problem is that I've recently realised that I need to be able to query this model with custom conditions, and if one of those conditions was realised in one of the modifications, the result wouldn't match because the model is searching the original record rather than the finally presented record. Example where I query the model to return rows where abc is blue (not grey):

在该示例中，模型直接查看abc为蓝色的行的原始数据，并且不返回此结果，因为蓝色值位于之后后附加的Modification中找到原始结果.

In that example, the model looks straight at the original data for rows where abc is blue and doesn't return this result, because the blue value is in a Modification which is attached after the original results are found.

我现在要做的是在预订模型的beforeFind()回调中添加一个查询，以查找与给定条件匹配的修改，并加入预订以确保其他条件仍然匹配.当在上面的示例中返回蓝色时，它将结果存储在数组中作为类属性，并继续使用常规的find()，但是排除该预订的ID不会返回(因为我们已经找到了它的最新版本).然后将它们合并在一起，再次在afterFind()中对它们进行排序.

What I've done now is put a query into the beforeFind() callback of the Booking model to look for modifications that match the given criteria, joining the booking to make sure that any other criteria still matches. When it returns the blue in example above, it stores that result in an array as a class property and continues with the regular find(), but excludes that booking's ID from being returned (because we've found a more up-to-date verison of it). Then it'll merge them together, sort them again etc in the afterFind().

这是可行的，尽管我希望它稍显冗长.

This works, although it's a little more long-winded that I was hoping for.

毕竟，我已经意识到，在此应用程序的其他部分中，有些模型正在手动加入预订表并搜索预订.因此，现在我需要一种方法，能够将这些修改直接合并到MySQL中所有直接连接到表的手动连接中，而又不影响原始数据，并且最好不要更改太多我的代码.

After all that, I've realised that in other parts of this application, there are models that are manually joining to the bookings table and searching for bookings. So now I need a way to be able to incorporate the modifications into all of those manual joins straight to the table in MySQL without affecting the original data and preferably without changing too much of my code.

我的想法是，我需要删除手动联接并创建一个模型关联.当我查询说具有很多预订(将修改应用于每个预订)的客户模型时，预订模型的beforeFind()和afterFind()仍然可以运行吗?

My thoughts were that I need to remove the manual join and create a model association instead. Will the beforeFind() and afterFind() of the Booking model still run when I query say the Customer model which hasMany Bookings (to apply the modifications to each booking)?

我的另一个选择是通过删除修改中可能包含的任何条件，从MySQL返回比必要更多的行，然后根据我的搜索条件使用PHP过滤结果.这个选项让我有些害怕，因为如果没有该标准，结果集可能会变得庞大...

My other option was to return more rows from MySQL than necessary by removing any criteria that might be contained in the modifications, then use PHP to filter the results as per my search criteria. This option scared me a little because the result set has the potential to be massive without that criteria...

如何实现此数据结构?我的主要要求仍然是，我不想更改原始的预订记录，而是在顶部添加修改记录，但是我需要能够通过该模型查询预订(包括修改).

How can I achieve this data structure? My key requirements are still that I do not want to change the original Booking record, rather add Modification records on top, but I need to be able to query bookings (including modifications) through the model.

我想尝试尽可能多地在后台进行这种集成，这样我就不必遍历整个应用程序来更改n看起来像这样的查询数:

I want to try and keep as much of this integration behind the scenes as possible so I won't have to go through my entire application to change n number of queries that look like this:

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

我希望能够隐式包括对预订所做的任何修改，以便在上述查询中返回最新的预订.

I want to be able to implicitly include any modifications made to bookings so that the up-to-date booking will be returned in the above query.

另一个问题是，将预订模型手动加入搜索查询时，如下所示:

The other problem is when the Booking model is manually joined to a search query, like this:

$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中手动联接表(修改集成位于Booking的before和afterFind()回调函数中)型号).

As you can see, the above query won't include the modification in my MSPaint example above, because it's manually joining the table in SQL (the modification integration is in the before and afterFind() callback functions of the Booking model).

在此方面的任何帮助将不胜感激.

Any help on this would be greatly appreciated.

我知道这已经足够长了，但是我想补充一点，我想跟踪这些更改而不更新原始记录的原因是，财务方面无法更改，因为它会影响报告.

I know this is long enough already, but I thought I'd add that the reason I want to track these changes and not update the original record is that the financial aspect can't change, because it will affect reporting.

到目前为止，我看到的最快，最简单的解决方案是，在所有情况下都将修改直接应用于原始预订，除非它会影响财务信息，但仍会作为修改进行跟踪(因为我目前不需要基于搜索有关此信息).

The quickest and easiest solution I can see so far is to apply modifications directly to the original booking in all cases except when it affects financial information, which is still tracked as a modification (because I don't currently need to search based on this info).

推荐答案

听起来您正在尝试实现 临时数据库 .临时支持是ANSI/ISO SQL:2011标准的主要新增功能之一. MySQL(像大多数RDBMS一样)落后于标准.将时态数据库视为CVS/SVN/Git的DBMS等效项.

It sounds like you're trying to implement a Temporal Database. Temporal support was one of the major additions to the ANSI/ISO SQL:2011 standard. MySQL (like most RDBMS) lags behind the standard. Think of Temporal Database as the DBMS equivalent of CVS/SVN/Git.

相比之下，我们使用的没有时间特征的传统数据库可以称为当前数据库.

By contrast, the traditional database we use without temporal features can be called a Current Database.

在当前数据库中，如果尝试实现临时支持，则可能会通过不同的方法以多种方式失败:

In a Current Database, if you try to implement temporal support, you can fail in many ways with different approaches:

• 单表方法.当您需要进行修改时，请对原始记录进行UPDATEs，除非您有某种本土的触发/审核逻辑，否则，历史踪迹不存在.即使您拥有审核/更改日志，也必须进行一些难看的挖掘才能重建更改历史记录.

• The one-table approach. When you need to make modifications, you do UPDATEs on your original records, and unless you have some sort of homegrown trigger/audit logic, the history trail is absent. Even if you have an audit/change log, you'd have to do some ugly digging to reconstruct the change history.

两表方法.无需就地进行修改，而是将数据分为两个表，一个表具有基本/原始记录(例如预订)，另一个更改/修改/变化量表.然后至少保留了原始数据，但是再次必须编写复杂的逻辑以查看具有分层修改的原始数据.如果只希望应用某些一些修改，那就更糟了.

The two-table approach. Instead of making modifications in-place, you split out your data into two tables, one with the base/original records (e.g. booking), and another table for your changes/modifications/deltas. Then at least you have your original data preserved, but again you have to write complex logic to view the original data with modifications layered on. It gets even worse if you want only some of the modifications applied.

预先计算的结果表方法.您保留3个或更多表:基本记录，修改表，以及一个试图始终具有结果的表(保留最新的基本+修改表).每次编写INSERTs时，都要编写触发器和过程来进行此计算，祝您好运；如果需要UPDATE或DELETE，Heaven将为您提供帮助.该设置非常脆弱，可能无法同步，例如死锁和回滚.如果您不使用触发器/过程在数据库中执行此操作，则可以尝试在应用程序代码中实现结果计算，但运气不错-多线程使用者可能会很难受.而且，仅应用了一些修改，您仍然无法轻松访问结果.

The precalculated resultant table approach. You keep 3 or more tables: the base records, the modifications, and also a table which attempts to always have the resultant (keeps up to date the base + modifications). Good luck writing the triggers and procedures to do this calculation whenever you do INSERTs, and Heaven help you if an UPDATE or DELETE is needed. The setup is fragile and could break out of sync, such as deadlocks & rollback. If you don't do this within the DB with triggers/procedures, you could try to implement resultant calculation it in the application code, but have good luck at that -- and it could get ugly with multi-threaded consumers. And still, you don't have easy access to resultants with only some modifications applied.

结论:如果您不限于MySQL，则应真正考虑使用具有内置时态支持的数据库.否则，您将重新安装轮子.

Conclusion: If you're not limited to MySQL, you should really consider using a DB that has built-in temporal support. Otherwise, you're going to re-implement the wheel.

这篇关于有效管理数据更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！