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?

|improve this question
  • The unique key needs to include the SKU and the record status. – Jerry Jeremiah Aug 2 '16 at 4:21
  • 1
    @JerryJeremiah cute but that breaks if I want to delete the same SKU twice. – candied_orange Aug 2 '16 at 4: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 2 '16 at 4:34
  • 1
    It's like you need the PK to be SKU + sku-version-number, which makes each one unique again. – Erik Eidt Aug 2 '16 at 4: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 2 '16 at 11:48
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?"

|improve this answer
  • Thats a great suggestion. So for product page, instead of deleting, user should set the record as inactive. Use "deleted" status for transactional tables, eg. invoice. Is that correct? – Reynaldi Aug 8 '16 at 8:22
  • @Reynaldi That's right. – Tulains Córdova Aug 8 '16 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.