I am looking for a job as a translator and decided to set up a database in which to keep all the data concerning employers, jobs, projects,etc.
I am having trouble how to design my database properly so here is the case:
I use MS Access 2003 SP2 on Windows XP SP3.
I have the major categories in my database:
1) translation agencies, job sites and agencies - these are all intermediaries for me but I have to keep records of them so that I can get in touch with the real client. I have called them MetaClients and their main table is tblMetaClient.
2) all persons and companies that provide me with text for translation - these are end clients for me, so I have called them EndClients and their main table is tblEndClient.
3) all translating jobs that I have I have included in a table called tblJob.
(take a look at the attached screenshot of my "Relations" window.)
Both to tblMetaClient and tblEndClient I added contacts tables to keep the data of the persons I got into contact at the respective company - I called the contact tables tblMetaContacts and tblEndContacts.
To both of the last two I added tblCorrespondMeta and tblCorrespondEnd to keep the date and the details of the conversation/correspondence.
My problem is the relations between 4 tables: tblEndClient, tblJob, tblEndContacts and tblCorrespondEnd:
1) I might have multiple jobs from a single end client,
2) I might have several contact persons from a single end client, and
3) I can have multiple conversations concerning a single job.
Is it correct to add a relation between tblJob and tblCorrespondEnd or how would you suggest to arrange the relations between tblEndClient, tblJob, tblEndContacts and tblCorrespondEnd