How to properly delete a record from a database?

Question

Database design

Good day.
There is a certain table:
`id`, `name`, `deleted`, `date_create`, `date_delete`
name - a unique key.
When an object is deleted, the record is not physically deleted, but marked as deleted. (`deleted`=1, `date_delete`=NOW())
If we try to insert a new record with a name(`name`) that has been deleted, we will get a duplicate key ... and everything is logical here.
Interested in approach to this situation in principle.
I came up with two and a half solutions to this problem:
1. If there is a remote record with the same name, then we restore it. But in this case, if there are bindings to this record, then we create an incomprehensible situation for the user. he will receive an object with a bunch of bindings that he did not previously create. Not very pretty.
2. Create an additional field a la `hash` and make it a composite unique key [`name`,`hash`]. The `hash` field is filled with something like "_" by default, and when removed, set a unique hash. (`hash`=MD5(NOW().....), `deleted`=1, `date_delete`=NOW()).
2.5 Almost the same as in paragraph 2, only without an additional field. Just concatenate the name with a unique hash when deleting.
So far, option number 2 seems to me the most simple and understandable, but there is a strong feeling that I don’t know something.
Please tell me how do you delete records?

Answer 1

There is a certain table:
`id`, `name`, `deleted`, `date_create`, `date_delete`
name - a unique key.

Remove uniqueness, since a new record with the same value will be new . If you need to distinguish between records, add "(str. 03/13/18)" to NAME.

Extended answer

CREATE TABLE [dbo].[CertainTable](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](100) NOT NULL,
  [DateCreated] [datetime] NOT NULL,
  [DateDeleted] [datetime] NULL,
  [NamePower]  AS (((([Name]+' (')+CONVERT([varchar],[DateCreated],(4)))+isnull('~'+CONVERT([varchar],[DateDeleted],(4)),''))+')'),
  [IsDeleted]  AS (CONVERT([bit],[DateDeleted])),
 CONSTRAINT [PK_CertainTable] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CertainTable] ADD  CONSTRAINT [DF_CertainTable_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO

Наличие зависимых полей deleted и date_delete — нарушение нормальной формы. Второе оставляем, первое вычисляем. Так же вычисляем наименование с датами.
Результат:

1 TEST 2018-03-11 00:00:00.000 2018-03-12 00:00:00.000 TEST (11.03.18~12.03.18) True
2 TEST 2018-03-13 08:31:19.143 NULL                          TEST (13.03.18)          NULL

Answer 2

If you want to do with `hash`, then:
1. Do UNIQUE index only on the `hash` field
2. Generate `hash` only from Name (MD5(Name)) when your field is not deleted. And when marking deleted, change `hash` to another algorithm (for example: MD5(Name + Date of delete)). In this case, uniqueness by Name will remain for non-deleted ones, and remote ones will not interfere. renaming meaningful fields is bad.
PS: If your name is unique, why do you need the ID field?

Answer 3

1) Remove uniqueness from the field, if it is possible to add the SAME name under a different id, then this violates the logic of work. If there is a need to keep the active state unique, check programmatically before adding a new record.
2) This is quite normal practice, a common task, for example, to record logins. Uniqueness is easier to check before insertion than to look for the causes of errors when duplicating keys.

Answer 4

With the smallest bloodshed: remove uniqueness from name and add uniqueness to the combination name, deleted, date_delete
(Unique keys can be added to several fields at once).

ALTER TABLE dbo.имятаблицы
  ADD CONSTRAINT uq_имятаблицы UNIQUE(name, deleted, date_delete)

PS Not entirely ideal, of course, that is, it will be necessary to ensure that non-deleted records date_delete would always be NULL, but in general, this will allow only one non-deleted record with one or another name to exist, but any number of deleted records with the same name ( if only date_deleted were different)

Answer 5

The legs of the deleted flag grow from ancient, ancient times, when a scattered collection of files of an almost regular structure was called a "database", and these files were stored on floppy disks and each action with this "database" cost resources, both temporary and mechanical in the form of head wear / floppy disks)
Today, the kernels of common DBMSs allow you to build full-fledged database schemas, indicating relationships between tables (constrains, references, cascades, etc.)
And, in general, there are no fundamental contraindications for real deletion, and not marking records.
If you want / want the history of operations - you can merge deleted records into a separate history table (perhaps by transforming and cutting data that is not relevant).