Prev: how to get rid of "Add new field"
Next: Fields
From: Lars Brownies on 17 Apr 2010 04:38
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

From: Tom van Stiphout on 18 Apr 2010 01:36
Your Relation table doesn't need an ID field if that ID is not used in other
tables. Often you can simply put the PK over both FK fields.

On the two kinds of Jobs: the classic solution is to have another table in a
1:1 relation with the main table, and it would hold the fields that are only
applicable to one kind of job.

-Tom van Stiphout
Microsoft Access MVP



"Lars Brownies" wrote:

> 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
>
> .
>
From: Lars Brownies on 18 Apr 2010 11:47
Thanks Tom.

Lars

"Tom van Stiphout" <TomvanStiphout(a)discussions.microsoft.com> schreef in
bericht news:E17309C3-44ED-4B81-A691-651E7D3394FB(a)microsoft.com...
> Your Relation table doesn't need an ID field if that ID is not used in
> other
> tables. Often you can simply put the PK over both FK fields.
>
> On the two kinds of Jobs: the classic solution is to have another table in
> a
> 1:1 relation with the main table, and it would hold the fields that are
> only
> applicable to one kind of job.
>
> -Tom van Stiphout
> Microsoft Access MVP
>
>
>
> "Lars Brownies" wrote:
>
>> 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
>>
>> .
>>
 | 
Pages: 1
Prev: how to get rid of "Add new field"
Next: Fields