Is it bad practice to have a "most_recent" boolean column in addition to a "create_at" timestamp column to track the latest version of a record?

Question

The table looks like this, it's SCD type 2:

+-----------+------------------+------------------------+
| id (text) | version (serial) | created_at (timestamp) |
+-----------+------------------+------------------------+

For 99% of queries we will be searching the entire table and filtering by additional columns and join tables. For these queries we're only interested in the most recent version of a record per unique ID. We will also be sorting by created_at and other columns.

To make it easy to find the most current records I was considering to add a most_recent (boolean) column as described in the answer here:

https://stackoverflow.com/questions/34495479/add-constraint-to-make-column-unique-per-group-of-rows/34495621#34495621

However I realized we already have the created_at column which tells us this information - we could use a DISTINCT clause in our search queries and order by created_date as described by @Svet's answer here:

https://stackoverflow.com/questions/17327043/how-can-i-select-rows-with-most-recent-timestamp-for-each-key-value

However, we'd then have to re-order the results by the column that we actually want to use to show the data.

It seems simpler in the long run to add the extra 'current' field, and like it would be more performant, but is it also bad practice?

Answer 1

it's a great practice

Marking and unmarking a record as most recent is trivial. Having a simple bit field to point you to the rows you care about is far easier than going out to find the most recent date for each group. Especially as data gets larger and larger, you'll kick yourself for not having this.

You haven't tagged the RDBMS you're using here, but all of the most commonly used ones support filtered indexes of some flavor, which would allow you to keep only the most recent rows indexed and easily accessible.

In SQL Server, that would look something like:

CREATE INDEX 
    whatever
ON dbo.some_table
    (key columns)
INCLUDE
    (is_most_recent)
WHERE
    (is_most_recent = 1);

Of course, you do need a way to guarantee that only one row will allowed to be active (most recent) per group. The safest way to accomplish that is with a unique index (sort of like the one above).

CREATE UNIQUE INDEX 
    uniquely
ON dbo.some_table
    (id)
INCLUDE
    (is_most_recent)
WHERE
    (is_most_recent = 1);

See also: What is the correct way to ensure unique entries in a temporal database design?

Answer 2

Another common pattern for SCD is to have a pair of datetime columns effective_date and end_effective_date. This allows both identification of the current version (using null or some fixed future date like 12-31-2099), and allows you to join using the business key and date, eg for building a fact table

select d.surrogate_key dim_bar_key, sf.* 
from fact_stage_foo sf
join dim_bar d
  on sf.business_key = d.business_key
 and sf.tran_date between d.effective_date and d.end_effective_date

Answer 3

It seems simpler in the long run to add the extra 'current' field, and like it would be more performant, but is it also bad practice?

If your table absolutely has to be SCD, it's a good idea.

For 99% of queries we will be searching the entire table and filtering by additional columns and join tables. For these queries we're only interested in the most recent version of a record per unique ID. We will also be sorting by created_at and other columns.

If that's the case, you might seriously consider keeping just the latest records in your OLTP database/table and move the SDC to the data warehouse (or at least another table).

Erik's answer suggests creating a filtered index, which, in essense, is doing just that: creating a shadow copy of subset of your data, slicing it both vertically (just the key and included columns) and horizontally (the filtering condition).

If you need to sort by created_at and other columns, you might want to create additional indexes that would help you sort by created_at and other columns. All these indexes (which are also shadow copies of subset of your data) would need to include your boolean flag as a filtering condition.

The good thing is that all these shadow copies will be maintained by the database automatically.

Here are some not so good things:

1. Your old records are still there taking up space in the heap/clustered index and in the buffer pool. If your query ends up not being completely servable by filtered indexes, it will have to spend IOPS, RAM, and CPU cycles on reading and filtering out historical data.

2. You will have to include the filtering condition into every query you write. In some databases, this might be eliminated by using a view, but not all databases support updating views.

3. The optimizer will less likely come up with efficient plans. Even if it does, it will take it more time to build these plans. If you are using Entity Framework or its friends, or generate a lot of dynamic SQL in other ways, your system will take extra performance hit, which can vary from negligible to serious, but will always be there.

4. In some databases (like older versions of SQL Server), the implementation of filtered indexes has a lot of bugs, especially if you use them in combination with things like the MERGE statement, indexed views etc.

The last three bullet points are important even if the table in question is small.

The downside of splitting the table in two is, of course, that you will have to create an extra ETL pipeline, or triggers, or otherwise keep these tables in sync. The upside is that your OLTP table will be smaller, simpler and easier for everyone (you and the optimizer) to work with.

If you already have a separate data source for analytical workloads, and your queries against historical data don't require transactional consistency, moving historical data out of the OLTP database will most likely make your life easier in the long run.

Answer 4

I'd discourage anything that requires extra bookkeeping or potentially leads to confused data. 3NF says that adding the flag is wrong since it is dependent on the create date—what row should you look at if a bug results in more than one row having the flag set, or the flag is set on a row that doesn't have the latest date? Also, an UPDATE of the existing row requires an UPDATE to reset the flag followed by an INSERT performed by the application.

My approach is a post update/insert trigger to set the last user/time/context, and a pre update/delete trigger to copy the original row to a history table.

This way the application issues INSERT, UPDATE, or DELETE operations as needed, but history is preserved. And queries only need to worry about history if they choose to. The current, most recent data is always in the main table.

I've used this model several times successfully on Sybase, SQL Server, and PostgreSQL. Indexes, table spaces, and partitions on the current table and the history table can differ according to application needs.

Answer 5

Modifying existing data when you create a new record is not good practice (depending on how secure your data is). If you need to keep old data around for security/audit reasons, you should treat it as write once.

Temporal Tables are what you are looking for. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16

Every time you update an existing record, the previous version will be moved to the temporal table along with when the update occurred, and your main table will only ever contain the latest version.
You can also query the tables to show what the data was like at a particular point in time.

Answer 6

If a new boolean most_recent column adds value or is more of a burden depends on the situation.

Compare this to adding an index to a table for faster lookups. Adding a new row will be slower because one more index needs to be updated. If you have many inserts but few lookups, the added index is more of a burden than an added value.

About the question: 99 percent of the queries will benefit from the new column, I guess that queries means lookups. In SQL, a query could also mean an INSERT query. But if there are many inserts, you will be very busy updating the most_recent column for the old rows. Also, there is more complexity, because the new column must be updated without error. And please declare that column with NOT NULL, to avoid having to check that value in queries.

Sounds perhaps a bit paranoia, but I have seen cases in production databases were more than one row had TRUE in the most_recent column, or other integrity violations. You know, at some point someone dumps a load of rows in the table to "repair" something, and does not update the most_recent column.

Conclusion: Yes it is good practice to add extra columns to make queries simpler. But that makes the data more complex, so after all the benefit is not as great as you hoped for, and could possibly end up negative. As a professional data engineer you have to use good judgement.