Wedge

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)

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

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.

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

So we're not worried about users doing crazy stuff like manually erasing rows from the members table?

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)

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)

And with the same problems attached. Why are these awesome things never simple? :P