L
Lars Brownies
In my recruitment database I have tblJobs, which at some point is linked to
a candidate via a relationship table. There a two category of jobs:
1. Jobs for which we did/do the recruitment.
2. Jobs which the candidate have had in the past (also most of the time
their current job) for which we have not done the recruitment.
The following fields we need for both categories, like:
ID job
Job description
Organisation
The relationship table is like:
ID relation
ID candidate
ID job
Date start
Date end
For category 1 only, we need to store extra information, like salary number
and other parameters. Also we need to be able to store one or more
requirements for every job of category 1. This should be done in an extra 1
to many table.
Category 2 is only used to do searches on candidates' history (on job
description and/or organisation).
I'm puzzled how to make a good database design. I can imagine I could store
category 1 and 2 in separate tables, because otherwise:
1. a lot of fields would remain empty.
2. I always have to to put filters in my queries, to filter out only
category 1. For instance, management reports will be most of the time about
category 1.
If I have 2 separate tables I'm guessing I'd need an extra relationship
table as well. That doesn't feel right.
Can someone give me advice which solution would be best?
Thanks in advance,
Lars
a candidate via a relationship table. There a two category of jobs:
1. Jobs for which we did/do the recruitment.
2. Jobs which the candidate have had in the past (also most of the time
their current job) for which we have not done the recruitment.
The following fields we need for both categories, like:
ID job
Job description
Organisation
The relationship table is like:
ID relation
ID candidate
ID job
Date start
Date end
For category 1 only, we need to store extra information, like salary number
and other parameters. Also we need to be able to store one or more
requirements for every job of category 1. This should be done in an extra 1
to many table.
Category 2 is only used to do searches on candidates' history (on job
description and/or organisation).
I'm puzzled how to make a good database design. I can imagine I could store
category 1 and 2 in separate tables, because otherwise:
1. a lot of fields would remain empty.
2. I always have to to put filters in my queries, to filter out only
category 1. For instance, management reports will be most of the time about
category 1.
If I have 2 separate tables I'm guessing I'd need an extra relationship
table as well. That doesn't feel right.
Can someone give me advice which solution would be best?
Thanks in advance,
Lars