I would very much appreciate anyone solving this, or pointing me in the right direction.
Please see attachment for current database design, the scenario is that:
each time a person logs in, the entire contents of the jobs table will be displayed
once a job is completed, it is moved to jobs_archive and relevant information is copied to commercials
there exists more commercials than jobs and jobs_archive combined since those are the expanded bits of the current databse
My question is whether I should merge jobs and jobs_archive and add a field stating whether the job is active or complete OR should I just leave as is.
The main reason for this design is because active jobs will be accessed all the time, so having the completed jobs in a separate table removes any stress from filtering out active jobs. Though I keep thinking it is poor design.