Results 1 to 14 of 14
  1. #1
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6

    Request to check relationships

    Hi all,



    Just wondering if someone would be so kind as to check the relationships between my tables. I'm really freaking out that I've gotten it all wrong.

    Here is a copy of my database

    The relationship criteria is:

    One uni can request many interviews from job seekers for a job
    One job seeker can receive many interviews for a job
    One uni can hire many job seekers for one job
    Each job seeker may only be hired by one uni for one job

    Would really appreciate this ASAP if anyone can help. I know that the database is not finished please don't comment on it. I'm just interested in the tables and relationships. Thanks so much

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I see some issues with your database. First, in the interview table you have a join between both the university table and the Job description table. Since a job is already tied to a university there would be no reason to have the university--interview join.

    Also, I would guess that a job might require many job skills and similarly, an applicant may have many job skills. With your current structure both the job and the applicant only have one skill. So you will want to take a look at that.

    With respect to the university table, you have the contact fields, but can there be many contacts at a university? I would think that would be the case, but your table structure will not support that. That then describes a one-to-many relationship which would require a separate but related table.

    The other thing I see is that you have fields in both the applicant and university tables that are the same. One rule of normalization is that like data should be in 1 table.

    So why not have a table to hold all people and their basic information? You can have a field that distinguishes an applicant from a university contact person or you can just tell who is who by how they related are related to the interview table. Of course, you may have the possibility that a current university contact person may one day be an applicant.

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName
    -txtAdd1
    etc.

  3. #3
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    Thanks for the reply. I noticed the University Interview join myself and have amended that. The many skills for applicants & jobs is a valid point however we were instructed to assume that each applicant and job require one primary skill. University is to have one contact per institute so that's also covered and the last point you made is also valid however we were told to keep both seperate.

    Thanks for the detailed input though. Following your review of my database, what would your opinion be on the relationships themselves? Are they structured as per the requirements I specified in my first post? Also, for the final requirement, what would be the best way of creating that relationship? Do I need another table??

    Thanks again for your help, much appreciated

  4. #4
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    To add my comment.

    A Table that is offen missed is one that Stores Locations. Town or City. Also Stae and perhaps Country. This could add three or so Tables to your structure.

    By doing this you eliminate Redundate Data and improve Integrity.

    Would also suggest that the Primary Key and Foregin Key have the same name.

    AddressID, CityID etc.

    Look into using Autonumber as your Primary Key. It is much more efficent.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...were instructed to assume that each applicant and job require one primary skill
    I take it from the above statement that this is a school project, correct?

    Are they structured as per the requirements I specified in my first post?
    From what little I know of the assignment, I would say that your structure looks OK, but Rainlover has some pointers that you should investigate to see if they might fit into the assignment as well.

    Also, for the final requirement, what would be the best way of creating that relationship? Do I need another table??
    Since only one applicant can be hired for one job from a university that is a one-to-one relationship, so having the hired field in the interview table should suffice.

  6. #6
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    It's not an assignment as such. I am in a course and we were asked to create the database to see where we are in terms of our ability before we get into the more difficult stuff. As you can see I'm having a bit of difficulty with some elements so wanted to get some better input on it.

    Thanks for the help. Just on the autonumber thing, is it not bad to use autonumber for primary key on every table? Surely its better to have a unique identifier for every record?

  7. #7
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I noticed in tblUni-Job and in tblJob-Skills that you are using a Composite Primary key.

    I would advise against this method as it can complicate things more than they need to be.

    PS Your Code Modules need attention. But at your request we will leave that alone for the moment.

    Use Autonumber instead

  8. #8
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    Thanks Rainlover,

    I had also gotten rid of those tables before your post. Thanks though. I've attached an updated version of what I've been working on might be more clearer for you guys.

    EDIT: What are code modules??

  9. #9
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Just on the autonumber thing, is it not bad to use autonumber for primary key on every table? Surely its better to have a unique identifier for every record?

    AutoNumber is unique within its own Table. I use it on every Table I create.

    I understand that using e.g. a Name of a person in two Tables, should never be used and I agree. But Autonumber is common practice. I would go so far as to say that most but not all good programmers use Autonumber in every table.

  10. #10
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    tblInterviews uses a Composite Primary key. Not a good idea.

    Also do not use Special characters in any Naming. You have used ?. Get rid of it.

  11. #11
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    Quote Originally Posted by Rainlover View Post
    tblInterviews uses a Composite Primary key. Not a good idea.

    Also do not use Special characters in any Naming. You have used ?. Get rid of it.
    Done and done thanks

  12. #12
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Good luck with the project.

  13. #13
    adohertyd is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    Thanks for all your input RainLover. Just one thing. What did you mean by code modules in your earlier post? How would I fix them?

  14. #14
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Firstly

    I would not use Macros.

    You should also have the declaration at the top of the page

    "Option Explicit"

    This will prevent the use of Variables without declaring what it is. Namely, Number, Text etc.

    But for what you are doing just continue with what you have, unless you really want to learn how to write Code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-03-2011, 12:51 PM
  2. Replies: 5
    Last Post: 05-20-2011, 06:18 AM
  3. Number operation request.
    By Playerpawn in forum Access
    Replies: 0
    Last Post: 05-18-2011, 11:29 AM
  4. Replies: 1
    Last Post: 11-23-2010, 10:41 AM
  5. request username and password
    By meysam_e2006 in forum Access
    Replies: 2
    Last Post: 07-02-2010, 02:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums