Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    31

    Answered: Relating my tables

    Hi Folks,

    I need help relating my tables. Here is the scenario.

    An employee can work in any of the five departments in the company. Each of the five departments has at least 30 areas of competence that the employee must be graded against for safety. scores range from 1-4

    I need to relate my tables so that i can:

    Create an 'main' employee form. On that form will be 5 buttons (one for each department). When clicked it brings up another form listing all of the areas of competence. for that particular employee.

    Can someone guide me on the relationships between the tables. ie where the primary keys go etc , what relationship type. I have attached a diagram showing the tables i have.

    I would also like to sum up all of the department 'competence' fields at some point - am i correct in thinking that you cant sum across table rows?

    Any help will be greatly appreciated..and many years of good Karma will come your way ;-)
    Attached Files Attached Files
    Last edited by MGF23; 12-18-15 at 15:18. Reason: missed a bit

  2. Best Answer
    Posted by healdem

    "competency list looks suspect to me...
    ...it is correctly named: a list of competencies but I'd expect a one row per competency

    this does assume that you have a list of competencies not all of which apply to every department

    I think you need to take a step back from the spreadsheet approach col1,col2,col3.... etc and instead think about a more db centric approach

    you have a table of employees, which contains a row per employee.
    you have a table of departments, which contains a row per department.
    you have an intersection table which departmental_employees, which as one row per employee in a department. there may be one or more employee values, there may be one or more department values. an intersection table is a way of modelling a many to many relationship. choosing a composite primary key (PK) of department_id and employee_id (the PK of the respective tables) you can guarantee that the data contained this intersection table will satisfy the following criteria
    1) it can only have department values which are already defined in table: departments
    2) it can only have employee values which are already defined in table: employees
    3) it can only have one row representing a pairing of department and employee.
    ..granted this holds assuming that you are indeed defining relationships as part of the table design AND that you have selected the enforce RI (realtional Integrity) option when defining the relationship.

    so defining a table for competencies..
    you then define a table for departmental_competencies, an intersection table so that you can only ever define a specific compentency as part of a departments data, and correspondingly fdor a given competency there can only be one per department

    the point of doing it this way is that it enforces data integrity. by defining what competencies a department should have (ie what competnecies an employee in that department should have) it then becoems easier to corral the data when you come to defien what competneci9es an employee may have.


    this is the slightly trickier bit because we don't know what you are trygn to represent. ferisntnace does a competnece apply to a specific employee department intersection ie:-

    employee: fred may know how to change a fuse in department XYZ but isn't deemed competent to replace a fuse in department ABC
    OR
    employee: fred is competent to change a fuse, and this is a requirement in departments ABC, CDE, XYZ. the way that competency is handled is very specific to the problem you are trying to model and somethign that no one here can comment on.

    depeding on how you choose to mdoel the competency reflects on how the final employee comeptencies table could/should be modelled. the answer may well be you don't actually need an employee departmental competencies table as competencies are the same department wide.

    so say your competencies were somethign like
    general waffly ones (eg elf'n'safety traned', first aid
    and specific ones eg
    set up millacron x8765 machining centre
    set up millacron xj76 machining centre
    set up millacron v091 press drill

    clearly soem are applicable to all departments
    but soem are only applciable to departments which have that specific requirement."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Designed properly you can do virtually anything. Cant open the attachment but in general terms.
    You will (probably) have a table for:-
    Employees
    Departments
    Competancies
    ....an intersection table that identifies what competancies a department should have
    ....An intersection table identifying what employees are associated with which departments.
    ....An intersection table that identifies what competancies an employee has.

    In a relational database each table will have a primary key. In an intersection table that primary key will probably be a composite of the primary key from its patent table. Eg for departmental competancies the pk would be department id and competancy id.

    When defining relationships in Access always, always sekectvthe enforce relational integrity option.

  4. #3
    Join Date
    Apr 2012
    Posts
    31
    Thanks for the feedback healdem. I have attached a jpeg of the relationship screen in access. In your view, is this the most 'elegant' way to relate the tables? I was wondering if i should put all of the different departments in one table?

    Thanks again for your time!
    Attached Thumbnails Attached Thumbnails relation.jpg  

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...In your view, is this the most 'elegant' way to relate the tables?
    No


    I was wondering if i should put all of the different departments in one table?
    Yes


    And whilst you are at it all the competencies in one table.
    Not knowing your assignment but id reckon on around 6 tables as identified in pist #2.

    As an aside, virtually whenever you see tables and or columns with what is ostensibly the same name but differing by a numeric or alphabetic suffix or prefix its usually a symptom of flaky design. Likewise columns or tables which refer to a specific date. The only exception to the latter is if for example that table refers to denormalised data which has been rolled up for performance or optimisation eg end of financial year summaries.

    sometimes it may be reasonable to have say 2 or 3 columns with the same name (eg phone_1, phone_2), but whereever you have such an approach ask the quetion is the limit of 2, or 5 reasonable. will lah lah land (the users) come up with a whizzo requirement for say 6 or more competencies. or add more departments.

    on your current approach you need to make data changes every time a new department and or competency is added/created.
    yes you may need to make report and or form changes but thats relatively rare in the flattened design approach Im suggesting. if fantasy island add a new department, then all 'they' need do is add the new department to the table, define the competencies for that department (and add any new competencies to the competencies table), define what employees work in that department and away they go. should be zero development effort as the data changes are precisely that changes to data by users for users. your approach requires new tables, new forms new reports (OK granted some of the new table / new report requirement can be ameliorated by careful/crafty initial design)
    Last edited by healdem; 12-21-15 at 07:41.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Apr 2012
    Posts
    31
    Hi Healdem. what you have described is exactly what i am after! Based on your thoughts i have redesigned the tables (and can now see that this is far more efficient way to go about things). The tables that i have been able to relate (successfully) generate results in a 'raw' format that is perfect for form creation later on. However, I cant seem to work out how to relate the two tables 'DEPT_Competencies' & 'employee_competency' (see attachment)..would you be able to advise? (As you have guessed I'm no Access expert- and this issue is becomming detrimental to the already eroding hairline ;-). Thanks again for your time!
    Attached Thumbnails Attached Thumbnails relate3.jpg  

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    competency list looks suspect to me...
    ...it is correctly named: a list of competencies but I'd expect a one row per competency

    this does assume that you have a list of competencies not all of which apply to every department

    I think you need to take a step back from the spreadsheet approach col1,col2,col3.... etc and instead think about a more db centric approach

    you have a table of employees, which contains a row per employee.
    you have a table of departments, which contains a row per department.
    you have an intersection table which departmental_employees, which as one row per employee in a department. there may be one or more employee values, there may be one or more department values. an intersection table is a way of modelling a many to many relationship. choosing a composite primary key (PK) of department_id and employee_id (the PK of the respective tables) you can guarantee that the data contained this intersection table will satisfy the following criteria
    1) it can only have department values which are already defined in table: departments
    2) it can only have employee values which are already defined in table: employees
    3) it can only have one row representing a pairing of department and employee.
    ..granted this holds assuming that you are indeed defining relationships as part of the table design AND that you have selected the enforce RI (realtional Integrity) option when defining the relationship.

    so defining a table for competencies..
    you then define a table for departmental_competencies, an intersection table so that you can only ever define a specific compentency as part of a departments data, and correspondingly fdor a given competency there can only be one per department

    the point of doing it this way is that it enforces data integrity. by defining what competencies a department should have (ie what competnecies an employee in that department should have) it then becoems easier to corral the data when you come to defien what competneci9es an employee may have.


    this is the slightly trickier bit because we don't know what you are trygn to represent. ferisntnace does a competnece apply to a specific employee department intersection ie:-

    employee: fred may know how to change a fuse in department XYZ but isn't deemed competent to replace a fuse in department ABC
    OR
    employee: fred is competent to change a fuse, and this is a requirement in departments ABC, CDE, XYZ. the way that competency is handled is very specific to the problem you are trying to model and somethign that no one here can comment on.

    depeding on how you choose to mdoel the competency reflects on how the final employee comeptencies table could/should be modelled. the answer may well be you don't actually need an employee departmental competencies table as competencies are the same department wide.

    so say your competencies were somethign like
    general waffly ones (eg elf'n'safety traned', first aid
    and specific ones eg
    set up millacron x8765 machining centre
    set up millacron xj76 machining centre
    set up millacron v091 press drill

    clearly soem are applicable to all departments
    but soem are only applciable to departments which have that specific requirement.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Apr 2012
    Posts
    31
    Hi Heldem,

    Thanks for helping with this. After re reading this thread (and doing a little homework) I was able to get the point of the junction/intersect table - I have attached a sample of the relationship I have settled on.

    Once again many thanks
    Have a virtual on me!
    Attached Thumbnails Attached Thumbnails final.jpg  

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
  •