Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2016
    Posts
    3

    Search Member Qualifications

    Hello - my first post (apart from introduction).
    My dB design skills are rusty, my relationship logic is confused.

    TABLES: 3
    ISSUE: Do I have enough fields in the right tables? My design is M:M correct?

    Members - mID, Name, link.contactDB
    Courses - cID, courseName, preRequisites
    Qualifications - mID, cID, courseName, qualificationDate, expiryDate

    Examples of use:
    a) Search for Members with "Chainsaw" and current "FirstAid"
    b) Identify members with recertification due in "september"

    NEXT ISSUE: If I wanted to filter the searches omitting "members who are absent" can that flag be set in the linked contactDB?

    Have I given enough detail for this problem?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Seems fine for a first stab at it
    ..except not sure why youd want to repeat the course name in tge qualifications table, when its already defined in courses
    Also your design infers that a course can have at most one prerequisite. Ypu may need to rethink that if a course can have more than one pq

    However i can see a problem with your future requirement of finding members who are absent. That requires a means of knowing wben someone is due to go on training.

    Being picky id suggest you use id in tbe table yhat defines the pk, and tablenameid for the fk. So in qualifications its member_id and coyrse_id. Decide warly on if you want to use CamelCase or underscored_words. Ie course_id or CourseId.

    Also the jury is out if you should store a qualufication date and expiry date, or store the qdate in qualufications and a validity in the course. If you are happy with your current model leave it. I would add a course validity in sny event to ghe course entity ti reduce the risk of the wrongexpury date being entered inadvertantly or maluciously. Which in turn means that for me ud drop the expiry date

    Decide early an abbreviation scheme. Its a PITA to write qualificationdate, pefhaps qual date, but instead use say validfrom.
    Last edited by healdem; 08-31-16 at 02:23.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2016
    Posts
    3
    Thank you Healdem for taking the time to consider my project.

    I was trying to not overly complicate the concept, but I suppose it does need a bit more explanation. (see my responses to your comments below)

    I've progressed my ideas and decided I need 5 tables (NOTE: italics links relationship in other tables).

    1. Contacts : contactID and various fields (ie. name, address etc.)
    2. Members : memberID, contactID, and various fields (name, status, role, availability, notes)
    3. Courses : courseID, courseName, validTerm (ie. 0 = no-expiry; n = number of months before expiry), courseDescription (i.e. mandatory/elective, prerequisites)
    4. Events : eventID, eventName, eventType
    5. Attendance : attendID, memberID, courseName, eventName, attendDate,

    My problem now is how to identify "skills" that were acquired/certified in "courses" that need to be maintained, and then track a member's practice of the skill.
    Members would do that by "attendance" of an "event". The Events are either a Course, MaintenanceTraining, or Deployment.

    Should I create another table "skills" or simply include "skills" as though they were additional courseName items?

    PURPOSE
    Search for members with required skills, for immediate deployment.
    Identify members who need opportunities to practise skills.
    Identify members who need recertification.
    Provide maintenance training for specific skills.

    Quote Originally Posted by healdem View Post
    Seems fine for a first stab at it ..except not sure why youd want to repeat the course name in tge qualifications table, when its already defined in courses...
    It's not a repetition I marked them in italics to indicate the relationship to field(s) in other tables)

    Also your design infers that a course can have at most one prerequisite. Ypu may need to rethink that if a course can have more than one pq
    I've decided prerequisite is not critical enough to flag. I will include them in a "note" field.

    However i can see a problem with your future requirement of finding members who are absent. That requires a means of knowing wben someone is due to go on training.
    The "absent/away" status is only needed for on the day deployment, not for training availability.

    Being picky id suggest you use id in tbe table yhat defines the pk, and tablenameid for the fk. So in qualifications its member_id and coyrse_id. Decide warly on if you want to use CamelCase or underscored_words. Ie course_id or CourseId.
    Wow! So many typos... it's hard to understand what you're referring to with "pk" and "fk". In any case I get your point about adopting good form in naming tables and fields. At this stage, the field names are arbitrary - just trying to keep it simple for the thread.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    pk: primary key. its the unique value in a table
    fk: foreign key. its a value that may, may !, be repeated more than once referrign to another table where that same value is the PK

    ferinstance in your model
    mID is a PK in members, and a fk in qualifications
    I woudl content that it should be called ID in members and memb_ID in qualifications, but thats just terminology and coding style/standards

    I've decided prerequisite is not critical enough to flag. I will include them in a "note" field.
    bad idea if you need to rely on the data. say your model defiens what the course prerequistes are,. then you can search for users who do / do not have those prequistes. stuffing them into a memo fiels makes it very very tricky to find which users have which pre requisties qualificiations.

    prerequisities is in my books a repeating value, and therefore according to normalistion rules deserves a table of its own (along with anythin else relevant to that intersection of qualification and requirements.

    as to typo's, well with me you either learn to live with 'em of find another person to assist
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2016
    Posts
    89
    Hi Anakowi,

    A suggestion, read threats about surrogate vs natural key. Links proposed but not exhaustive of course
    http://www.vertabelo.com/blog/techni...e-primary-keys
    http://www.agiledata.org/essays/keys.html

    And to summarize, it's better to use a surrogate key and for ensure uniqueness of each instance of entity, define a unique index on the natural key

Posting Permissions

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