This topic was marked solved by its starter, on May 23rd, 2013, 12:47 AM
Storing the modified-post user id

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Storing the modified-post user id
« on October 20th, 2011, 11:58 PM »
For a reason I've never understood, SMF never stored the user id of a modified post. It stores the name and the time - the bare minimum, I guess - but not the user id.

I figure we should store the user id and actually add that to the list of ids we query during Display startup, so that we can actually display not only the current name (as opposed to whatever name they used at the time) but we can link to it.

It also allows for some other interesting consequences, like if the last person who edited your post was a moderator, it could relatively easily prevent non-moderators then editing that post.

There is a storage cost (~3 bytes per message) and a slight performance hit though I'd argue that fairly often the editor on a post is someone who has already posted in the topic (recently) anyway, e.g. the author, or a moderator...

Thoughts?
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Storing the modified-post user id
« Reply #1, on October 21st, 2011, 12:09 AM »
That's interesting.
As for the storage cost, I'd suggest not storing the user name, instead store only the ID, and if the user is deleted, err... Well, I guess it wasn't very important then :P

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #2, on October 21st, 2011, 12:12 AM »
The poster's name is still stored too, remember. We could store just the ids, and if the relevant account gets deleted, backfill those rows with the relevant names.

I guess, though I'm not sure, that the reason it's done that way is to allow guests to edit their post after the event (based on session and/or IP address, but not sure off the top of my head)

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Storing the modified-post user id
« Reply #3, on October 21st, 2011, 12:19 AM »
Quote from Arantor on October 21st, 2011, 12:12 AM
The poster's name is still stored too, remember. We could store just the ids, and if the relevant account gets deleted, backfill those rows with the relevant names.
Oh yes, I forgot than an empty varchar takes no space (or so) in the DB, so it'd only be used when deleting the account...
Then it's easy enough :)

id_modified_by or something should have a default of 0 or maybe null, I don't know which is likely to take the least space...

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #4, on October 21st, 2011, 12:24 AM »
Quote
Oh yes, I forgot than an empty varchar takes no space (or so) in the DB, so it'd only be used when deleting the account...
Well, a varchar consumes n+length bytes where n is the length of it, so a 5 byte varchar consumes (1 to hold the length and 5 for content =) 6 bytes. An empty varchar will still contain that one byte to reflect on the fact that you're not keeping any content and thus its length is 0.

The bigger problem I have is the amount of rewriting that will occur on deleting an account, because it will force every row where that user has impacted to be rewritten - which could cause a lot of data change.

What I'd probably do in that case is make it so all account deletions have to be approved by the admin, rather than making it optional, because of the performance impact that's attached (the account's frozen out to the user anyhow at that point unless they want to undelete it), and for spammers it's mostly irrelevant because in a lot of cases the post will be removed as well...
Quote
id_modified_by or something should have a default of 0 or maybe null, I don't know which is likely to take the least space...
Use 0. NULL is a special case and requires an extra bit per column on top of whatever data requirements the column has itself. If you don't have any need for the NULL value, don't use it and save that bit per column as it means that you spend less time fighting with bit shifting to get anywhere.

Nao

  • Dadman with a boy
  • Posts: 16,079

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #6, on October 21st, 2011, 10:05 AM »
Sure it is. It's just a case of whether or not we do actually keep the name or not or just store the id until the name is needed.

Mind you, there is a part of me that thinks the name should be saved anyway to guard against idiots who manually delete stuff from tables because they're idiots and don't really know what they're doing...

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Storing the modified-post user id
« Reply #7, on October 21st, 2011, 05:03 PM »
Just store the ID, really. No need to waste space for now... (an unmodified post in SMF will take 1 byte because of the varchar(0), while an unmodified post in Wedge will take 4 bytes or so, so might as well try to save space.)

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #8, on October 21st, 2011, 05:05 PM »
So we're not worried about users doing crazy stuff like manually erasing rows from the members table?

Nao

  • Dadman with a boy
  • Posts: 16,079

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #10, on October 21st, 2011, 05:21 PM »
Because users are, generally, stupid and do crazy crap like that? That's the only reason I can assume for storing both the id and name of normal posters.

Though as mentioned, the cost of updating on delete account is non trivial (especially on MyISAM for big posters, because it has to lock the entire messages table, which is a blocking op, until it can modify the relevant rows etc. - much less of a problem on InnoDB where locking is row level rather than table level)

Nao

  • Dadman with a boy
  • Posts: 16,079
Re: Storing the modified-post user id
« Reply #11, on October 21st, 2011, 06:23 PM »
Just because you removed your accounts on many forums doesn't mean big posters usually do that :p
Plus it'll only update their modified posts...

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Storing the modified-post user id
« Reply #12, on October 21st, 2011, 06:24 PM »
Quote from Nao on October 21st, 2011, 06:23 PM
Just because you removed your accounts on many forums doesn't mean big posters usually do that :p
Plus it'll only update their modified posts...
Not *many* forums, but some. And I know I wasn't the first on sm.org to do that (including former team members)

But yes, it will only update their modified posts (and their regular posts if we also do the same thing to the regular name use... which we're not doing right now)

Nao

  • Dadman with a boy
  • Posts: 16,079

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278