Databases
StackOverflow2013
Postsdbo
POAre there problems with this 'Soft Delete' solution using EAV tables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAre there problems with this 'Soft Delete' solution using EAV tables?
    primarykey
    data
    text
    <p>I've read some information about the ugly side of just setting a <code>deleted_at</code> field in your tables to signify a row has been deleted.</p> <p>Namely<br> <a href="http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/" rel="nofollow">http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/</a></p> <p>Are there any potential problems with taking a row from a table you want to delete and pivoting it into some EAV tables?</p> <p>For instance.</p> <p>Lets Say I have two tables <code>deleted</code> and <code>deleted_row</code> respectively described as follows.</p> <pre><code> mysql&gt; describe deleted; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | tablename | varchar(255) | YES | | NULL | | | deleted_at | timestamp | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ mysql&gt; describe deleted_rows; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | entity | int(11) | YES | MUL | NULL | | | name | varchar(255) | YES | | NULL | | | value | blob | YES | | NULL | | +--------+--------------+------+-----+---------+----------------+ </code></pre> <p>Now when you wanted to delete a row from any table you would delete it from the table then insert it into these tables as such.</p> <pre><code> deleted +----+-----------+---------------------+ | id | tablename | deleted_at | +----+-----------+---------------------+ | 1 | products | 2011-03-23 00:00:00 | +----+-----------+---------------------+ deleted_row +----+--------+-------------+-------------------------------+ | id | entity | name | value | +----+--------+-------------+-------------------------------+ | 1 | 1 | Title | A Great Product | | 2 | 1 | Price | 55.00 | | 3 | 1 | Description | You guessed it... it's great. | +----+--------+-------------+-------------------------------+ </code></pre> <p>A few things I see off the bat.</p> <ol> <li>You'll need to use application logic to do the pivot (Ruby, PHP, Python, etc)</li> <li>The table could grow pretty big because I'm using <code>blob</code> to handle the unknown size of the row value</li> </ol> <p><strong>Do you see any other glaring problems with this type of soft delete?</strong></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USShane Stillwell
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI don't have much of an opinion on whether it is a good thing or not (at least not without a lot more details about your requirements) but depending on your database server you can probably do this with triggers and keep the logic out of the database.
      singulars
      1. POAre there problems with this 'Soft Delete' solution using EAV tables?
      1. USTom Micheline
    2. COTom, I haven't stumbled on how to do a pivot with triggers, do you know of any good examples online?
      singulars
      1. POAre there problems with this 'Soft Delete' solution using EAV tables?
      1. USShane Stillwell
    3. COSorry, not off the top of my head. It also very much depends on what db you are using.
      singulars
      1. POAre there problems with this 'Soft Delete' solution using EAV tables?
      1. USTom Micheline
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload