I have a members table with an id column that auto-increments. There are only two unique columns, the id column and the email column. However, I will have a script run once a week that removes accounts that have expired. I would like suggestions and considerations on how to handle this. Should I just delete those rows from my table, or should I just mark those rows inactive in someway (like having a Boolean column for expired with yes/no as the values), or should I move those rows to another table, like “expired_members”. I ask because the auto-increment function provides a good snapshot of some statistics (eg, # of registered users) and perhaps other things.
I’d mark as expired (is_expired), you can always physically remove or archive at a later day should the table size become an issue; you can’t put 'em back.
Never rely on the auto increment id for anything; it’s a completely meaningless value and should be treated as such. If you need the number of users just do a count(id) on the table. MyISAM tables store this value so retrieval is a simple lookup. On InnoDB it’s a bit slower but still pretty fast.
When you do use the “active” column, there is a nice trick; use tinyint and then use 0 for not active and 1 for active, then when you need to know the number of active users just do SELECT SUM(active) FROM users
