Instantly restore deleted records
First things first
In this blog I am going to have a quick look into why anyone would want to deal with obsolete data and then in more detail into the ways to do so. And since I’m targeting a FileMaker audience, some of my statements will be FileMaker specific and may not apply generally to database design best practices. Also, for clarity, this blog is not about backing up your data and backup policies. It covers only practices used within a live application. Regarding key usage, all table examples use a numeric field named ‘__id’ with a unique non-empty validation rule.
Why delete records?
So why would we want to worry about obsolete data? Here are some of the common believes:
To save disk space
With today’s storage media prices this is really one of the last concerns. If you are running out of disk space, it’s usually a sign of very underestimated hardware requirements. Deal with this by upgrading your system, not downsizing your data.
Speeding up queries (searches)
While this is a valid concern, a properly indexed table can contain millions of records before we get a noticeable performance hit here. Even if considered that FileMaker limits the options of optimizing indexes and queries, in a typical solution we hardly get to a point where the number of records has a considerable impact on query performance if searching on indexed fields.
Now, after ruling this out, we’re left with the last major reason: hiding obsolete data from the user. For example: making an obsolete article unavailable for order creation, a client unavailable for invoice creation and so.
Common techniques to hide obsolete data
Here are the most common solutions from my least favorite to the one that I prefer most and which was the reason for writing this blog in the first place:
Delete the data
To be brief, I would say: NEVER DELETE ANYTHING from a database. If you do, sooner or later you end up with inconsistent data, invalid foreign keys and what’s worse, the data is gone and there is no way to restore accidentally removed records, except of digging it up from a backup. There are some exceptions though, mostly log tables and such. After identifying obsolete data here, you can delete it pretty safely.
Moving records to an archive table
In most database systems this is an idea worth considering. Unfortunately, in FileMaker there are 2 very serious obstacles:
- FileMaker does not natively support what is known in SQL as a ‘UNION’ statement. It allows to merge the contents of two or more tables together as if it were one table. This is useful if you want to combine the contents from a live table with the contents from an archive table. (As a workaround in FileMaker, you could design virtual tables and populate those using SQL, because then the Union statement becomes available…)
- But there’s another disadvantage to his: you can not automate any development on both live and archive tables. Any change in one must be manually reflected to the other.
Marking data as obsolete
This is a very common solution. You add a checkbox, say ‘Active’, and you consider records for which this field evaluates to ‘False’ as obsolete. While fast and simply to implement in the data definition, this technique brings lots of extra work in the user interface. Every found set has to be manipulated to filter the obsolete data out. Think of list layouts, portals, user searches, scripted actions…
Another disadvantage is that the native FileMaker record navigation toolbar still displays the total number of records (active and inactive records). This may be confusing to users of you application, so you might want to hide that toolbar and rebuild all that functionality as well…
Using an Active Records table
Finally, the most elegant solution: create a new ‘active records’ table and put it in front of the real data table. Store only primary keys in that active records table and link it to the real data table via a one-to-one relationship. Here’s an example with an article table where you want to hide obsolete articles:
Create the Article table the same way as usually, but DON’T activate the serial number auto-enter option for the __id field. Then, create another table, ‘ArticleActive’ and define only one field: the __id field there, with the serial number generation turned ON. This table will contain one record for each active article. Here is what the Relationships Graph should look like:
This will work because we have turned on the creation of records on the left side of our ARTICLE~Active – ARTICLE relation. As you can see, with little extra effort we have created a robust data structure that uses only native FileMaker features and provides a very high level of data consistency.
Step by step
Here’s how it works: we’re deleting a record from the ‘Articles’ layout, based on the ‘ARTICLE~Active’ table.