Database design pointers (please!)

[Pritcham]

Hi

I'm currently having to design a database for a recruitment agency
that's just started up and have one area where I'm a little unsure
where to go.

Basically I've implemented the 'standard' Customer, Contacts tables
linked on CustomerID, and also have CallRecords (for phone calls etc
made to contacts) Linked on ContactID.

My difficulty is that they want to be able to store names/details of
people looking for work (candidates) BUT these people may also be a
contact (i.e. the agency could be dealing with a contact at a company
who is also looking for a new job themselves). They would also like to
(naturally) have these candidates details held against 'current
employer' customer details so there may be situations where a candidate
is JUST a candidate (i.e. not currently working and therefore not
associated to a company), OR they may be a candidate AND a contact, and
you may have contacts who are JUST contacts (i.e. not actively looking
for work at the moment).

I'm basically just trying to figure out the options I have for storing
the contact details and candidate details.

FYI I need to store the same details for Contacts and Candidates (i.e.
name, job title, contact numbers etc) but Candidates require extra
information to be stored about them (work experience, qualifications
etc).

Any help/pointers would REALLY be appreciated!!

Thanks in advance
Martin

[Erland Sommarskog]

Pritcham (do******************@hotmail.com) writes:

My difficulty is that they want to be able to store names/details of
people looking for work (candidates) BUT these people may also be a
contact (i.e. the agency could be dealing with a contact at a company
who is also looking for a new job themselves). They would also like to
(naturally) have these candidates details held against 'current
employer' customer details so there may be situations where a candidate
is JUST a candidate (i.e. not currently working and therefore not
associated to a company), OR they may be a candidate AND a contact, and
you may have contacts who are JUST contacts (i.e. not actively looking
for work at the moment).

I'm basically just trying to figure out the options I have for storing
the contact details and candidate details.

FYI I need to store the same details for Contacts and Candidates (i.e.
name, job title, contact numbers etc) but Candidates require extra
information to be stored about them (work experience, qualifications
etc).

My gut reaction is that you should have a Persons details, where you
have all common information about persons, and then Contacts and
Candidates would refer to that table and augment it with the specific
information.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp