Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Key problems with tables

    Hi, I am new to databases and I'm having some trouble. I've attached a diagram so you can see what I'm talking about:

    http://i20.photobucket.com/albums/b2...o/database.jpg

    What I'm trying to do is create a database with employees, their certifications, the organizations the certifications come from, and so on. One employee can have many certifications, one certification can be held by many employees, etc. I've done some searching online and I'm confused - do I need seperate tables to hold these relationships? Don't keys point to tables (i.e. that the empID FK in the certification table points to a number of employees?).

    Say I want to start simpler, just employee and certification: name of employee in one and certification name in the other. How can I set up many to many relationships between them, so that I can look up the employee and come up with all the certs held by him?

    Thanks,
    James

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    You're thinking too hard. What does a certification consist of? Well, a title, a governing organization and perhaps a description? What does an employee consist of? An employee id, basic name/contact information and perhaps a title. How do you put those together? EmployeeCertifications, containing a certification id and an employee id.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    Well that makes sense, but I believe that I would need an entire table of certifications. Well, maybe not. This is to be backend to an application, and the user needs to select the certification from a dropdown I would be calling from a database, the certification table. So maybe keep an entirely seperate table for all certifications on record, then copy that certification to a field for the employee?

    Here's the thing though - I want to search by employee, certification, or organization, or any combination. So I think at the very least, the certification table and organization table would need to be related, right? So I can say that Microsoft points to MCSE/MCSD/etc and IBM to WebSphere/Modeler/etc, and the certificates should point back to the organization that gives them. Or just a table with all certs and a field with the company?

    If I searched by user "James F" and organization "IBM" I want all IBM certs that I have.

    I'm new to database construction, and the help I find is over my head to a certain extent. Maybe seperate tables then just compare the values?

    I want to make this database clear, easy to maintain and upgrade, and fast and well thought out. That's why all the debating! I haven't designed one before

    Thanks,
    James
    Last edited by JimmyFo; 12-05-05 at 16:06.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    What you described can be done with four tables: Employee, certification, organization and employeecertification

    Take a crack at figuring out how to link 'em up...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2005
    Posts
    6
    Teddy, here's what I've come up with.
    Ignore the other tables, they are just exercises in the same fashion

    Thanks for your help,
    James
    Attached Thumbnails Attached Thumbnails database.jpg  

  6. #6
    Join Date
    Dec 2005
    Posts
    6
    Oh, and if that's right, if I want to have a new employee, a new certification, and a relationship between them, what do I change? I assume I create the new employee, the new certification - but then the foreign and primary keys in the Emp_Cert table... are they set automatically?

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Looks good to me, barring all those exam tables of course.

    I'm not sure I understand your question with regard to new employees and certifications.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2005
    Posts
    6
    I'm just confused as to how the relationship table works. For each employee/certification relationship, do I need a unique identifier in the Emp_Cert table? So if Employee A has 10 certificates, there are ten rows in the Emp_Cert table, each with a unique id and pointing to the respective certifications and employee? That would, I assume, be the table I search.

    Thanks,
    James

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Yup, you've pretty well got it. Standard junction table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2005
    Posts
    6
    Thanks, my man! Big help!

Posting Permissions

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