Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16

    Four Tables, Many to Many

    I am designing a database for tracking training requirements/qualifications. I'm wondering how I can query several tables for reports or if maybe my design approach is flawed.

    Tables (not full descriptions):
    Employees (ID, name, active, qualifiedPosition1...~qualifiedPosition22)
    Positions (ID, positionTitle, requirement1, ... ~requirement50)
    RequiredSubject (ID, Title, Description)
    TrainingRecord (EmployeeID, RequiredSubjectID, Date)

    The training will be recurrent (qualification expires).

    I'm not sure, though, how to query these tables for the following reports:
    The requirements for a given position and if an employee is trained (up to date) to each of the required subjects for that position.
    Which employees are trained (up to date) for a particular position or subject.

    Can this be handled by join and/or union queries or will I need to use cursors? I am using MS Access.

    TIA,
    Bruce Hyatt

  2. #2
    Join Date
    May 2016
    Posts
    89
    Hi Bruce

    Could you upload your logical data model and even better conceptual data model?

    A First glance, your data model seems not to respect modeling rules because you repeat some fields by adding a number

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Without looking at a LDM or CDM, virtually everytime you see a column with the same prefix, but a numeric or alpha suffix you can be certain the design is not normalised
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Essentially you have two pathways of data. One is the template, the model of what training / certification should / must have, focussed on a role. The other is the training / certification a person does have, naturally focussed on a person.
    The two pathways cross over / intersect where you define the role(s) a person has and therefore a means of comparing the template requirements to the individuals attainments.
    Depending on your requirements the indivuduals attainments could be just the most recent for each requirement or each and every attainmemt
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    here's a possible approach. I don't claim its 'right' / 'wrong'. its not fully developed, and as ever with these sort of suggestions we dont' know the detail of your requirement

    it makes several key assumptions
    a role may have zero, one or more required certifications. There are no either or certifications for a role, if you need that functionality then you may need a sub table of the required certification to handle equivalency. there's nowt stopping you defining a required certification as optional.
    an employee may have zero, one or more roles. it is your call as to whether those roles are concurrent and or historic. if an employee can only have one role then move the role_ID attribute into the employee table.
    an employee may have zero,one or more certifications, which may or may not be pertinent to a current or past role. ie an employees training record is separate from the role(s) as currently defined. eg an employee may hold say a first aid certificate which is irrelevant to their current role, but maybe relevant in future roles.
    the valid from in the employees_certificates allows you to have multiple instances of the same certificate, but each with a different valid from date. if all you need to know is the last certificate, wheter its current or not then remove valid_from fromt he PK and put your preferred method of identifying whether its current as an attribute. the only reason why I think you might need to store the history is if say you had to do a retrospective search to find out if employee mrthnmn had certificate xyz on a specific date. if you don't need that complication don't model it. this assumes that any re certifications are the same material. IE that if you allowed a re certification that was a different course code then you would have a problem. thats an additional layer of complexity.

    from the template of what a specific role should have you can match against what an employee does have using the appropriate join.
    left joining the roles certifications (ie get all the certifications for a role against employees certifications (via the employee role table) will give the picture for that employee. ie if NULL they don't have that certificate.
    using an appropriate where clause (ie where the RHS of the left join will contain NULL will identify certificates they don't have but would need for a role), a simple join would return all certificates that employee has for that role

    table and column names are named in full to try and make the approach more legible, and not neccessarily suitable for use in a real data model. ferinstnace no abbreviations have been used. note the styling of the intersection tables eg 'employees_roles', and also the use of the parent tables name when referrign to FK 'role_id', 'employee_id'
    mrthnmn.pdf
    Last edited by healdem; 08-16-16 at 06:39.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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