How to void (soft delete) a record in database in a scalable fashion

Currently I have a units table with the following columns:

• id: PRIMARY Integer
• name: String
• serial_id: String
• . . .

As well as a statuses table. This Unit's data is sent to the server from our server. The statuses table has the following columns:

• id: PRIMARY Integer
• internal_temp: Float
• battery_level: Integer
• unit_id: FOREIGN KEY Integer
• serial_id: String
• ...

Currently, I want to void out old units, so that way I can save new units with duplicate/same serial_id. However, I still want to preserve the relationship that voided units and statuses have and maintain the relationship/data in an organized, scalable manner.

My first idea, was to simply add is_void to the units table and then do something like:

Unit.where(serial_id: serial).find_by_is_void(false).statuses

but, I am not sure if this will scale well. Currently, a unit makes nearly 2000 status entries per day.

I was thinking along the lines of creating a join table that can manage the relationship between the unit and status but I was unsure and wanted to seek some advice.

If you're using Rails 5 or lower, you could use the paranoia gem to implement the soft delete functionality. This gem requires us to add a column called deleted_at to the model which we want to soft_delete. It then uses a default scope to fetch only those records which have not been soft deleted. It provides various utility methods to easily fetch deleted records, really destroy records etc.

In case you are using Rails 6 or want to implement the soft deletion feature by yourself, here's what you would need to do:

1. Add a column called deleted_at to the model which you want to soft delete
2. Add a default scope in that model as default_scope -> { where(deleted_at: nil) }
3. You could then write utility methods to check if records are deleted or not, or scopes to fetch those records which are deleted as well. This API could be anything that you want
4. For scalability, you could even extract out all the common methods that you would want, associated with soft deleted models, into a concern which could simply be plugged in wherever required

As you can see, this implementation is extremely similar to what the paranoia gem provides out of the box. In case you're doing this for learning and have lots of time, I'd recommend building your own feature. If this is something that needs to be deployed soon in a professional project, I'd recommend using the gem since it may speed up the process.

This is a nice article which describes the paranoia gem and its features: Rails Soft Delete with Paranoia gem