Re: Structure problem

RayK <keattchr_at_pissbtinternt.com> wrote in message news:<Xns929B653D8CFEEraymondkeattchpisssc_at_10.0.0.4>...
> Jackal wrote in news:5c81527d.0210010424.12e2b8fd_at_posting.google.com:
>
> <Snip>
> >
> > Hello Ray,
> >
> >
> > One employee has done several jobs in the past:
> > Employee 1-n JobHistory
> > One employee has several CV's:
> > Employee 1-n CV
> > One CV refers to several jobs in the Job history of de Employee:
> > CV 1-n JobHistory
> >
> > Translated to tables and their primary keys:
> >
> > Employee(empid, ...) -- employees
> > JobHistory (empid, jobid, ...) -- employees job history //
> > (empid) refers to employee
> > CV(empid,cvid, ...) -- employees cv's // (empid)
> > refers to employee
> > CVJobHistory(empid, cvid, jobid, ...) -- Jobs on cv's //
> > (empid,cvid) refers to CV; (empid,jobid) to JobHistory
> >
> > (this last table represents the relationship CV 1-n JobHistory )
> >
> >
> > I hope this helps;
> > I hope I have understood your problem :-)
> >
> > Regards,
> > Carlos
> >
>
> Thanks a lot for helping me out on this one - it is driving me nuts!
>
> You have understood my problem spot on but there is one thing that I am not
> understanding.

> My Staff department want to create several Cv's for each
> employee.

That is the CV table above. Being (empid,cvid) the primary key, you can heve more cv's voor one employee.

> Each CV will have a selection of past jobs on it, tailoring the
> CV for the client.
>

That is de CVJobHistory table.

>
> The details of a job will contain items like StartDate, EndDate, Details.
> If these fields were put in the JobHistory table it would mean I would have
> to repeat data several times. It appears to me I need a seperate Job table
> to hold the basic job details - but I don't know how to fit this in to the
> database, if its needed at all!

What you are calling the Job table is JobHistory in my schema. The PK is (empid, jobid);
so for eacht employee you can have diferent jobs. (A job has to belong to an employee)
This is the 'real' Job table
Attibutes StrartDate, etc should be there. I don't see why this data wil repeat; Each row in this table is a diferent a job

You could try this:
Maybe choosing other primary keys wil help to simplify things; we also rename JobHistory to Job,
and CVJobHistory to CVJob

Job      - PK (jobid)
           (column empid is a foreign key to Employee, required!)

CV       - PK (cvid)      
           (column empid is a FK to Employee, required!)

CVJob    - PK (cvid,jobid)
           (no need of empid here then!
            empid is in the cv cvid is refering to)
*******************************************

StartDate, EndDate, Details, etc. go in de Job table Maybe you like this beter!?, but it's really the same structure.

Regards,
Carlos Received on Wed Oct 02 2002 - 16:29:25 CEST