Table Design - Identical tables for different statuses

Question

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.

Answer 1

In my experiences with similar things (tables of scheduled jobs and tasks), everything is in one table. There is usually a status column, but jobs of all statuses are in the same table (sometimes there's an index on status). As for primary key, why not just use a sequential ID? I've seen that work very well. You can also have as combined key of draft number AND actual job number, unless there's some other requirement I'm not understanding.

If you think this will improve performance, make sure you have measurable problems that can only be solved this way before you make any changes. Also try to prove that this method results in actual necessary performance gains.

Answer 2

I agree with @FrustratedWithFormsDesigner: this is something you should handle with one table. It doesn't sound like there are radical differences in attributes for a job in different steps, so there's no entity-related reason why you'd have separate tables.

Regarding performance, if you are using postgresql or SQL Server, you could use a filtered index and filter on status. This is helpful if only a very small percentage of your jobs are active. Even if you are using a database product which does not have filtered indexes, a regular index might very well be all you need.

Alternatively, you could ship older jobs off to an archive table, where they're available if you need them but don't slow down your queries on present data. But I'd go down that road only if there is an actual performance problem.

• Joseph Caruana: Thanks for your response +1 - for addind more details to the previous answer