Atmosphere: Jboss, Mysql, JPA, Hibernate

Our web application is going to be serving a lot of customers (~ 1,000,000) and you will find a plenty of child table where user specific data are saved (e.g. personal, health, forum contributions ...).

  1. What will be the best practice to archive user &lifier user specific information. [a] Will it be smart to maneuver the aged user &lifier user specific information for their particular tables inside the same database (e.g. user_archive, user_forum_comments_archive ...) OR [b] Can you just mark the database records having a flag within the original table(s) and merely query only non aged records.

  2. There exists a unique constraint on User.loginid, how can you handle this requirement when the customers are aged via 1-[a] (i.e if your user with loginid 'samuel' will get moved in to the archive table and when a brand new user will get added with similar title within the original table, how does one prevent this. What will be the best technique to address the initial key constraints.

  3. There exists a requirement to selectively archive records and produce it back if required, are you going to depend on database tools are can you handle this via your persistence APIs uncovered through the JPA entity model.

Personally, I'd choose solution "[a]".

Getting things split on two table sets (current and aged) will make things a little difficult to manage when it comes to common RDBMS concepts (example: forum comment author will be a foreign key pointing towards the user's table... however, you can't possess a area work as an overseas answer to two different tables).

You can get a compromise (customers table uses solution "a", the rest of the tables like profile get aged to some twin table like per solution "b") but this could make things unnecessarily complicated for the code (in some instances you need to consider the non-aged, in certain towards the aged only, in certain other cases towards the union of both).

Solution A would easily solve #2 and #3 needs, too. Originality of user title is simple to enforce if everything is incorporated in the same table, and resurrecting aged customers is only a matter of flipping a little (Aged=Y/N) around the primary user table.

10% isn't much, I doubt the difference when it comes to performance would actually justify the additional complexity (and chance of bugs).

I'd put an aged flag up for grabs after which produce a view to make use of when you won't want to see aged records. This way people could be more consistent in using the archive flag I suspect.