What is the preferred method for an updated by field in a table?

Question

Just wondering if there's a preferred method or Best Practice for storing "Updated By" type fields in a database. I tend to see just the name of the user being stored pretty frequently, but I've been designing my tables with the ID of the user who last updated that table.

Is there a reason to use one method over the other? I like my method for having a nice backreference to get to the user... on the other hand it also generally means you can never delete the user (if you also use this for Created By anyway) so maybe that's enough reason not to? Not to mention how messy diagrams or ORM mappings seem to get when everything has to point back at the one table...

Answer 1

If you have a way to tie the update to a user that's represented by an entity in the database, then use that. If you don't have that sort of mechanism in place (no users in the database, for instance), then storing the username is fine.

Answer 2

Your method is sound. You wouldn't want to delete a user (rather archive them with an "Active" bit field). This way you still retain the historical record, even if that user is inactive.

Answer 3

Definitely store an id instead of a name. Otherwise what do you do if someone wants to change their username for some reason?

In most systems users should never be deleted. Just have an "Active" flag, or something equivalent.

Answer 4

use the User's ID and not the User's Name, it takes less space. Don't delete the User row from the DB, use a status field to mark them as deleted.