CODEWITHSUNDEEP
database-design
CodeRedick
CodeRedick

Reputation: 7415

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

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...

Upvotes: 4

Views: 122

Answers (4)

RossFabricant
RossFabricant

Reputation: 12492

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.

Upvotes: 6

KM.
KM.

Reputation: 103607

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.

Upvotes: 3

madcolor
madcolor

Reputation: 8170

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.

Upvotes: 3

Adam Robinson
Adam Robinson

Reputation: 185643

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.

Upvotes: 4

Related Questions