Design question what pattern to use

Question

Problem Description: We have a requirement to store the snapshot of an entity temporarily in the database until a certain period of time until all the processes to approve the data are completed. Once all approvals are completed the data shall be permanantly persisted into the actual table.

Example:

1. Consider an entity called "User". One way to create the user is through the "Create Account Process".
2. The "Create Account Process" shall capture all the information of the User and store it in a temporary table in the database.
3. The data shall be used by the "Account Approval Process" to run its verification process.
4. After all the verification is completed successfully, the User data shall be persisted to the actual table.

Question: Where to store the user data entered during "Create Account Process". Additionally, User data should be editable till the verification process is complete.

Answer 1

I would usually have a single User table with a column "Approved". Most queries will only retrieve rows where this flag is true. E.g. you could create a view "ApprovedUsers" as

SELECT ... FROM Users WHERE Approved = 1

(where ... is all columns except the Approved column).

Then you don't need to mess around with two tables. Most clients other than the account approval process would have access to the "ApprovedUsers" view but not to the underlying table.

Answer 2

It really depends. When you expect many users registering and you have big Users table with many columns and you need many joins, you might want to have separate table.