I am developing a transactional System and the below is a simplified scenario of my problem.
- Users enter JOBS in a system.
- A User first enters a JOBS with a DRAFT status (and the Job is given a draft number)
- When the user submits the JOB, the status is changed to SUMBITTED (it is given an actual number)
- Finally when the JOB is completed, the status is changed to POSTED.
I was thinking of creating 3 separate identifical 'JOB' tables for it. when a Job changes from draft to submitted, I move the data. This is mainly for performance reasons. Most queries would be on the current JOBS and this would ensure that its table is not cluttered with all the posted. Furthermore such a division of tables would provide structure improvement because I can use the JOB number as the primary key. If there was a single table, the draft number would need to be changed to an actual number. And primary keys should never change.
Now, my question is - does my approach make sense? is it customary to have separate tables? If anyone had experience with this and can provide feedback, that would be great.