3

First of all, I'm sorry if the title is a bit confusing.

I was reading this question (Is it a bad practice to have a "record status" column in a database table?), and like the idea of using record status for some tables. So intead of deleting records from the table, I would set the record status to "deleted". My question is if, for example, the record is in the product table. And the product table will have unique "SKU" column. If a record is marked as deleted, then user won't see that record again. In the future, user might want to insert a new record with the same SKU as the one that was previously (logically) deleted. But it will still raise Primary key duplication in database. How should I handle this situation?

Community
  • 1
Reynaldi
  • 201
  • The unique key needs to include the SKU and the record status. – Jerry Jeremiah Aug 02 '16 at 04:21
  • 1
    @JerryJeremiah cute but that breaks if I want to delete the same SKU twice. – candied_orange Aug 02 '16 at 04:29
  • Ok I obviously didn't think hard enough. My tables have a record status column but they also have a change user and change time column - and of course my primary key includes all of them so just having record status isn't actually enough... – Jerry Jeremiah Aug 02 '16 at 04:34
  • 1
    It's like you need the PK to be SKU + sku-version-number, which makes each one unique again. – Erik Eidt Aug 02 '16 at 04:55
  • Use just a unique ID as the identifier of the record is a practical solution. Also allows you to fix a wrongly entered SKU for example. – Luc Franken Aug 02 '16 at 11:48

1 Answers1

3

Usually logical deletion of records is used when the PK of the table is a autoincremental sequence.

I think the product table is not a good candidate for a logical "deleted" column. That solution is better suited for things like invoices.

That said a good solution is telling the user: "The product you are trying to insert already exists, although with an inactive status. Do you want to bring it back to an active status?"

Tulains Córdova
  • 39,449