Tell me how best to store history in the database? Suppose there is a table of Products, and this table is related to all sorts of orders and so on. After some time, the company is no longer going to release any product and something needs to be done with it so that it is not displayed anywhere, but the data is the need could be obtained (for example, revenue for this product, orders, and tn).

What is the best way to deal with such records? I have an idea to mark them with a special feature IsDeleted and do filtering on it, then it will not appear and it will be possible to get information if necessary. How correct is this approach?

And another such question: Suppose a product can have 1 of 5 types. On the one hand, I can list them in CHECK, and on the other, I can create a separate entity under types. Which option is more correct?

  • 2
    I like your idea with the same note, to fence a copy of all the tables for the sake of the archive and the separate work with it is too expensive. For some cases, you can view and make that will show only working records. On the second question, if there is even the slightest probability of the appearance of the 6th type - make an entity. Nobody bothers, by the way, instead of ID, use 1-2 character codes, if it suddenly looks clearer - Mike

1 answer 1

On the first question. You are on the right track! Create a column and specify the type boolean.

On the second - you are also on the right track! Separate table with types of goods. And in the table with the goods, create a column where the row identifier with the type (type id) is indicated.

  • Regarding is_deleted, in secret, in all databases this is done + the date and time of deletion is stored. The truth is often called simply deleted and the boolean type, of course - Ryder95
  • If + is the date of deletion, then this very date can be used as a sign of deletion - Mike