Structure problem

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!

Thanks

Ray Keattch. Received on Thu Sep 26 2002 - 05:19:20 CDT