0

Background: I am trying to solve one simple problem. I have a database with two tables, one stores text (this is something like articles), and the other stores the category to which this text belongs. Users can make changes to the text, and I need to save who and when made the changes, also when saving changes, the user writes a comment on his changes, which I also save.

As I have done now: I added another table to which I save everything related to changes, who made the changes and when, as well as a comment on the changes, and the ID of the text to which the changes apply.

What is the problem: Deleting the text also needs to be recorded in history, but since in the records with history there is a foreign key with a check, then I have to delete the entire history that is associated with the text, so as not to get an error.

What I have tried else: I tried to add an attribute to the table with the text "Deleted", and the row is not physically deleted, but the "Deleted" = 1 flag is simply set, and at the same time I can save the history, and even the moment of deletion. But there is another problem, the table with the text has an attribute "Name", which must be unique, and if the record is not physically deleted, then when I try to insert a new record with the value "Name", which already exists, I get a uniqueness error, although the old record with such a name is considered remote.

Question: What are the approaches to solving the problem, in which it is possible to save the history of changes in another table, even after deleting records from the main table, and at the same time keep the uniqueness of some attributes of the main table and maintain data integrity.

I would be grateful for any options and hints.

Aleksandr Kurilov
  • 343
  • 1
  • 4
  • 10

2 Answers2

1

A good practice is to use a unique identifier such as a UUID as the primary key for your primary record (ie. your text record). That way, you can safely soft delete the primary record and any associated metadata can be kept without fear of collisions in the future.

If you need to enforce uniqueness of certain attributes (such as the Name you mentioned) you can create a secondary index (non-clustered index in SQL terminology) on that column in the table and then, when performing the soft delete you can set the Name to NULL and record the old Name value in some other column. For SQL Server (since 2008), in order to allow multiple NULL values in a unique index you need to created what they call a filtered index where you explicitly say you want to ignore NULL values.

In other words, you schema would consist of something like this:

  • a UUID as primary key for the text record
  • change metadata would have a foreign key relation to text record via the UUID
  • a Name column with a non-clustered UNIQUE index
  • a DeletedName column that will store the Name when record is deleted
  • a Deleted bit column that can be NULL for non-deleted records and set to 1 for deleted

When you do a soft-delete, you would execute an atomic transaction that would:

  • set the DeletedName = Name
  • set Name = NULL (so as not to break the UNIQUE index)
  • mark record as deleted by setting Deleted = 1

There are other ways too but this one seems to be easily achievable based on what you already have.

Mike Dinescu
  • 54,171
  • 16
  • 118
  • 151
  • Thanks for your answer, this is really what I need. But I wanted to make sure once again that deleting a record using an attribute (rather than physically deleting a record) is the normal approach, or is it not a very good approach? – Aleksandr Kurilov Feb 20 '21 at 19:33
  • "normal" is so relative.. yes, it is used successfully in many implementations (usually called soft delete) for the same purpose you are using it, namely maintaining history. However there are also other practices which may be better depending on circumstances (ie. you denormalize and move deleted data to a separate table). In fact, you could also denormalize the edit history as a whole and store completely separate, using the UUID as a key. There is no "right way" and it really depends on ALL of your requirements and the scale of your system etc. – Mike Dinescu Feb 20 '21 at 19:41
  • some pretty good public debate on the merits, or lack thereof, of soft deletes: https://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea -- notice the top two answers are pretty much even split with slight favor towards not doing it but other commentary below goes back and forth: as long as you understand the implications I would say the biggest factor in your decision should be the expected scale of your system – Mike Dinescu Feb 20 '21 at 19:56
0

In my opinion, you can do it in one of two ways:

  1. Using the tables corresponding to the main table, which includes the action field, and using the delete , insert , update trigger of main tables for filling.

ArticlesTable(Id,Name) -> AuditArticlesTable(Id,Name,Action,User,ModifiedDate)

  1. You can use the Filtered unique index (https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15) on the “Name” field to solving your issue on adding same name when exists another instance as deleted record
Bijan Ghasemi
  • 296
  • 1
  • 8