Re: Structure problem

From: RayK <keattchr_at_pissbtinternt.com>
Date: Mon, 30 Sep 2002 15:06:39 GMT
Message-ID: <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. Received on Mon Sep 30 2002 - 17:06:39 CEST