Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    Ruse, Bulgaria
    Posts
    1

    Unanswered: Database Design Problem

    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
    Attached Thumbnails Attached Thumbnails RelationsJobSeeker.JPG  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mdragganov View Post
    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.
    You need to create junction tables to implement many-to-many relationships among the tables. See for instance: Database Design - Many-to-many
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •