0

I have a table with thousands of entry and want to show if the entity is deleted or not.

I can add a new column "isDeleted" in the existing table and update every entry(thousands) of that entity in the table once it is deleted

OR

have a new table for the deleted entries and join the tables for queries. I want to know which is faster.

I will be querying from the table and want the information about deleted entities as well as non deleted ones.

Lets say my table has columns:

id type prop1 info1
1 A any any
2 B any any
3 C any any
4 A any any
5 B any any

And i go and delete the type A, now I can have a isDeleted Column in this table only, as such

id type prop1 info1 isDeleted
1 A any any true
2 B any any false
3 C any any false
4 A any any true
5 B any any false

or have a new table for deleted types.

with the first method I will have to go and update the isDeleted column for every instance of type A, and there are 1000's of such entries. whereas in the second method i can simply add a new row in the new table.

I want all such unique "types" that have not been deleted from my table. but dont want to remove the deleted types information

I hope this is clearer

Tushar Vardhan
  • 9
  • 3
  • 1
    Well if you actually do a SQL `DELETE` there will be no row to set `isDeleted` . You will need to provide more detailed explanation of what "...entity is deleted..." means? Add as update to your question. – Adrian Klaver Jun 01 '22 at 18:27
  • Boolean column `isDeleted` for soft deletion is enough, separate archive table is not reasonable due to excess complexity. You may partition the table by this column for to improve the working with actual data only. – Akina Jun 01 '22 at 18:47
  • I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Jun 01 '22 at 18:50
  • Saying something is deleted when it is not is not good. Keeping them around in the table is saying you want to retain the information for use later on in that table, which is not deleting them. So either `DELETE` the types and have a `ON DELETE` trigger that moves them to a audit/history table where you can retrieve the information, if needed, without tripping over them in the original table. Or add an `active` `boolean` field and mark that `true` or `false` which is what you are really doing if you keep them in the original table. – Adrian Klaver Jun 01 '22 at 19:54

2 Answers2

2

The easiest way would be just to add an isDeleted column which is nullable and mark those that you delete as non-null. This would assert backwards compatibility also.

To build on this further, I would instead recommend to make this column into a deleted_at column stored as a nullable timestamp - this way you get the bonus of some extra metadata.

One such benefit of this extra metadata could be for audit trails.

muhrahim95
  • 121
  • 4
  • Good answer. But it seems that adding two columns for one purpose may be overkill. Why not just add the (nullable) deleted_at column, and if it is not null then the row is not deleted???? – Dennis Jun 01 '22 at 20:55
  • Thanks for the feedback @Dennis. I have edited it to be more clear - I did not mean two extra columns but exactly what you said. – muhrahim95 Jun 02 '22 at 08:19
0

To prevent repeated storage of the same data, add a different table types with columns type and is_deleted. This way, you can avoid inconsistencies, such as when rows 1 and 4 in your proposed example disagree with each other (one is true, another is false).

REFERENCES:

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
  • That's my question. Both methods have promise, if I add a new table, ill have to join the table for every query If I add a column then I have to update every instance of that "Type" which will be thousands. How do I decide which is optimal? – Tushar Vardhan Jun 02 '22 at 06:07
  • @TusharVardhan Can you use a view, which has these two tables, joined together? – Timur Shtatland Jun 02 '22 at 11:13