Separate table for 'scheduled payments' and 'completed payments'?

Question

In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute them, and update that row to status completed.

I've kept wondering why they didn't have a separate 'completed transactions' table? Is that bad practice?

Answer 1

There is one entity - financial transaction, that can be in a various states. Completed, scheduled, postponed, rolled back etc. State is the property of the entity, only part of the whole, like the field is a part of the row.

When the state is just a field of the table it is possible to create DB structure and code that are invariant regardless of how many states are possible. In the opposite case each new state produce the new table and lot of code should be rewritten to reflect the changes.

Answer 2

You are right to wonder. When you have an entity that can take two states (let's call them A and B) where one transitions directly into the other, but not the other way around, you should generally implement it in two tables.

The reasons splitting into two tables is a good idea is:

1. If A always transitions to B, then you will have a lot more B than A. This creates skew, which increases the likelihood of bad query plans
2. When you have transitioned to B, it is often the case that the row is now readonly. Whereas rows in state A are malleable. By having the readonly rows in a separate table, you can improve your backup strategy quite a bit.
3. With two tables, you can have separate indexing strategies for the two states. Often, you can get away with fewer indexes on the table that holds state B. This saves space.

An alternative approach to having two tables is to partition the table, with one partition for A and another for B. Some database allow separate indexing strategies on different partitions.