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:

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

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

CodePudding user response：

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

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

CodePudding user response：

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:

• What is the reason to "normalize your databases"?
• What is Normalisation (or Normalization)?