[thelist] Database history - handling references to rows which don't exist any more?

[Jay Turley]

Hi all-

I'm working on a application which keeps a log of some user events while
they use the application.

If the user adds or deleted a new item to the database (among other actions)
using the application, we store that information in our history table.

At some point, people need to look at the history of themselves or other
users.

Requirements state that users should be able to see things like:

- added Item #17 to database
- deleted Item #48 from database.
- uploaded picture to Item #925

Therefore we are saving the primary key of the items in our history table
like so:

user_history table
- id PK INT autoincrement
- user_id FK INT references users.id
- item_id FK INT references items.id
- foo VARCHAR
- bar VARCHAR
- yaddayadda BOOLEAN

However, because items may be deleted, but we have as a requirement to STILL
SAVE the history entries for that item, I am running into trouble.

This is because I am building links pointing to the items on the fly, i.e.
if there IS an item_id, then I pull the item info from the database using
that item_id and then output HTML like

<a href="http://examplesite.com/items/view/[item_id]">[item_name]</a>

Obviously OnDeleteCascade is no good, because it would delete the history
record. OnDeleteSetToNull is not the answer either.

I'm toying with creating a trigger that would automatically create a
non-linked version of the above, ie:

<span class="deleted">[item_name]</span>

and store it into the table in a "deleted_item_text" field or something like
that.

But before I go down this road, I'm wanting to see if anyone has any best
practices or ideas on how best to handle this! Help?

Thanks!!

-Jay