table design soft delete RRS feed

  • Question

  • hi, experts,

    today, I face a problem on database table design

     the record requires soft delete ,i.e. remain record in database. a column in the db table requires uniqueness.

    and the unique constraint has been applied to the column in the db table (due to other reasons, unique constraint  must not be removed)

    commonly, I think a 'deleted' column will be added to the table for soft delete.

    however, if people insert again the record with same 'deleted' value. how to handle?

    today, my colleague said there is a common practice to add timestamp as prefix to the value and then update the record to bypass the unique constraint restriction

    e.g. before delete,

    value = ABCDE, deleted= 0;

    after delete,

    value = 20160420. deleted = 1

    is this practice really good? common? not regard as trick?

    thank you very much!


    Wednesday, April 20, 2016 1:04 PM