Tombstone design pattern
The tombstone design pattern is software engineering design pattern for insert-only database deletes. Deletions are implemented by recording each deletion in a deletion (or "tombstone") table. For immutable updates, see the snapshot pattern.
Example tombstone implementation
Take a typical CUSTOMERS
table that does not implement the tombstone pattern:
Retrieving and deleting records in the CUSTOMERS
table is easy, however deletes destroy the data that was previously stored:
-- Retrieve a customer:
SELECT firstName, lastName
FROM CUSTOMERS
WHERE id = 1
-- Update a customer, but lose the original value for firstName!
DELETE FROM CUSTOMERS
WHERE id = 1
Using the tombstone pattern, we move the customer deletion to a new, timestamped table:
Retrieving and deleting customers is now a bit trickier, but in exchange for this added complexity we preserve the original customer data:
-- Retrieve customer data via LEFT JOIN:
SELECT CUSTOMERS.created, CUSTOMERS.firstName, CUSTOMERS.lastName
FROM CUSTOMERS
LEFT JOIN CUSTOMER_DELETES on CUSTOMERS.id = CUSTOMER_DELETES.id
WHERE CUSTOMER_DELETES.id IS NULL
-- "Delete" the customer data
INSERT INTO CUSTOMER_DELETES
(id, timestamp)
VALUES
(1, strftime('%s','now'))
Deeper Knowledge on Tombstone Design Pattern
Snapshot Design Pattern
A software design pattern for immutable data updates
Broader Topics Related to Tombstone Design Pattern
Insert-only Databases
A database design approach that requires deletes and updates to be performed as inserts
Snapshot Design Pattern
A software design pattern for immutable data updates
Design Patterns in Software Engineering
Common solutions to common problems in software engineering