Re: Structure problem

RayK <keattchr_at_pissbtinternt.com> wrote in message news:<Xns9299A4156B439raymondkeattchpisssc_at_10.0.0.4>...
> Jackal wrote in news:3d96ed0b$0$9899$e4fe514c_at_dreader1.news.xs4all.nl:
>
> > "RayK" <keattchr_at_pissbtinternt.com> wrote in message
> > news:Xns9295735D5F356raymondkeattchpisssc_at_10.0.0.4...
> >> Can anyone help with a problem with a database structure?
> >>
> >> I am writing a CV database. We need the facility to store more than
> >> one CV for each employee (we want to tailor an employees CV depending
> >> on the client they are going to) So I have the following tables...
> >>
> >> 1 Employee
> >> 2 CV
> >> 3 Job
> >>
> >> An employee has many CV's. A CV is for an employee
> >> A CV can have many jobs on it. A job can be on many CV's
> >>
> >> So I created a link table between CV and Job - JobHistory. Tables are
> >> linked like this (1-8 denotes 1 to many)
> >>
> >> Employee 1-8 CV 1-8 JobHistory 8-1 Job
> >>
> >> I met a problem when designing a form to creat a CV. One list shows
> >> all
> the
> >> jobs done by that employee. An Add button allows jobs to be
> >> transferred to another list on the same form so the user can add only
> >> certain jobs to
> that
> >> CV.
> >>
> >> Because no EmployeeID info is in the Job table I don't know what jobs
> >> to display on the CV form! I hope I have explained this clearly
> >> enough so somebody could suggest what my structure should be like!
> >>
> >> It seems wrong to creat a relationship between employee and job!
> >
> > As I understand it an employee has done several jobs in the past
> > and you want to be able to say which of those jobs wil be on each
> > CV of the same employee.
> > then... jou have to create a relationship Employee 1-8 Job.
> >
> > Employee 1-8 JobHistory (complete history of employe)
> > Employee 1-8 CV
> > CV 1-8 JobHistory (partial job history for eacht CV)
> >
> >
> Thanks very much for the suggestion. Please could you explain a little more
> because the relationship between a couple of the tables looks strange to me
> (newbieish to this game!) You are correct that an employee could have many
> different CV's. Each CV is tailored to the client so each job may appear on
> many cv's for that employee.
>
> Reading it one way then EMPLOYEE to CV is MANY to MANY through the
> JOBHISTORY table. Reading all the relationships individually gives ;
>
> 1 to many EMPLOYEE to JOBHISTORY
> 1 to many EMPLOYEE to CV
> 1 to many CV to JOB HISTORY
>
> Please could you explain the relationships you suggest a little more just
> so I can understand your reasoning?
>
> Thanks
>
> Ray Keattch.

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)

I hope this helps;
I hope I have understood your problem :-)

Regards,
Carlos Received on Tue Oct 01 2002 - 14:24:42 CEST