Duplicate data in SQL

Question

I have to keep duplicate data in my database so my question is...Is it preferable to keep the duplicate data in the same table and just add a column to identify the original data or I have to create another table to hold the copied data?

Answer 1

I suggest to save the duplicate data in a different table or even a different schema so it won't be confusing to keep working with this table.

Imagine yourself in six months form now trying to guess what are all this duplicate rows for.

In addition those duplicate rows does not reflect the business purpose of this table. It will be nicer to store them in a table named [table_name]_dup or a schema named [schema_name]_dup