How to handle expired free SaaS accounts

Question

This is a little philosophical, however, here is the script and the related question. Suppose you sell premium accounts and at the same time offer free, free accounts. Users register and register using their email address. Creating a free account does not require the transfer of any excessively sensitive data (only by email). Free users have X days to evaluate your service, then either switch to a premium account or see how a free account expires.

Question: what is the best way to handle this "obsolete" database?

You can:

1) save the account in the global table "user", marking it as expired

• PRO: The username / email address is always unique, and you cannot re-register using the same email address
• CON: you cannot re-register the same email address (maybe he wants to do this only after the new feature that he is interested in is added)
• PRO: all accounts are in one place, it’s easier to process search statistics
• CON: user table may grow over time

2) delete the account, perhaps by moving it to the user_history or expired_user table

• PRO: your user table is smaller and contains only data from live users
• CON: The expired username / email address is reused (your logs are likely to be confused and you should always register the user id and then the username as it will no longer be unique).
• PRO: the username / email address of the expired account is reused (expired users who want to give a trial version of another launch after adding new features can do this without having to choose a different email address).
• CON: collecting user statistics is complicated.

Has anyone encountered the same problem?

Answer 1

I would suggest dividing the circuit into two parts:

TABLE: users user_id (PK) email_address password_hash .... TABLE: user_status user_status_id (PK) user_id (FK) status_date status_value 

The current status of this account matches the current status. When a user subscribes to a "free" account, you insert the account into user_status with the status "new_free_status"; when the account expires, you insert an entry with the status "free_account_expired". You use the status to check if the user can log in or not; if you want people to register at least one month after the expiration of the free account, you check the user status record to see when their account was closed.

You can, of course, create another lookup table called "status" and join the table with "account_type" - this way your data will become more self-describing.

The key point in this project is that you want to separate the user profile from the current state and keep track of this status over time. This allows you to answer questions such as "how many people signed up for a paid account after a trial version?", "How long do people renew between signing up for a free account?", "How many users return for another test?" and etc.

Answer 2

Having a user table too large should not be a problem - the storage is cheap, and if it is well indexed, everything will be fine. I am currently developing a similar application, and we just collect all the accounts in a user table. If a user has missed the trial version for more than a month or so, we will just let them sign up again to check out the new features if they want, and we just activate their account.

This strategy works well, of course, due to the type of application. Usually you will use it daily; you will never use our application for several hours and throw it away again. That's why this makes sense to us, but Adobe doesn't make sense using Photoshop, for example.

What I said may not apply to your situation, but I (and I can only assume that other developers) consider it improper practice to use multiple tables to categorize the data. To do this, use the WHERE column and WHERE for what they are intended.