Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks for your response +1 - for addind more details to the previous answer –  Joseph Caruana Nov 20 '12 at 9:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.