Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: DB Design - Set/List of Requirements or prerequisites

    Hello everyone!

    I'm using Access 2007, and am rather new to the whole Relational Database thing.

    Anyway, I have a list of "trainable skills" which each have 3 properties:

    • Skill Name
    • Current Level
    • A list of prerequisite skills and the required levels for each


    For example, you could have something like:
    Name: Ethnic Relations
    Current Level: 0
    Requirements: Corporation Management (Level 2), Social (Level 3)

    I'm trying to figure out how to design a table or set thereof that can easily organize the requirements. Should I just have as my column headers:

    Skill Name, Level, Req 1, Req 1 level, req 2, req 2 level, ... , Req n, Req n Level, etc?
    Last edited by Wetmelon; 06-18-11 at 14:08. Reason: Fixed bullets, clarity

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have an association / junction / intersection table that allows multiple pre requisite skills to be defined

    eg
    Code:
    table: Skills
    ID (PK [primary key])
    Name
    
    table: Prerequistes
    SkillID (PK) 'identifies the skill requiring a prerequiste skill / qualification
    RequiredSkill (PK) 'identifies the skill / qualification that must be done before doing the SkillID.
    for both SkillID and RequiredSkillID declare them as foreign keys pointing to the Skills ID column. both should be the priomary key for the requisities table (that measn you cna only have the same prerequisite appearing once.

    this approach allow you to have multiple pre requisite skills (or for that matter none), which are easily searchable

    if you need to add a level then do so for each element.. so you woudl need to deifne level in Skills and level for both SkillID and RequiredSkill. those levels should also be in the primary key which now becoems a composite of 4 columns
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    So, essentially, I have 10 skills with 5 levels each. I should have 50 separate ID'd skills in Skill, with 5 of each containing the same value in the Name field?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    good point, well made

    you could / probably should sub type the level to another table. as you have correctly identified you should avoid repeating information.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Posts
    3
    So, on Skills table I have:

    Skill (PK)


    On the Prereq's table I have:

    Skill (PK)
    Prereq (PK)

    Both of which are FK's of Skills.Skill.

    Say I have 10 skills, that means I have 10 elements in Skill. If each one has two Prereq's, then I have 20 rows in Prerequisites, with Skill listed twice for each one? That doesn't sound right...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ultimately its your design, it depends on what you need to do with the data

    whether the final design has one, two, ten or ten thousand tables / rows / whatever is largely irrelevant providing the design meets the requirements.

    At present I suspect you need 3 tables
    skills 'detail of skill
    skilllevels 'identifies a skill and level
    prerequisites 'identifes what skills are pre requisites for a specific skill + level

    but you could just as easily justify one table if you felt you didn't need to (easily) search the prerequisite skills + levels. the point is you have to justify what is needed for your design requirement.

    the real advantage of separating out the pre requsitites is that this design supports as many prerequisites as required (from 0 to whatever). the prerequisites are easily searchable

    out of curiosity why do you feel "that doesn't sound right"?
    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
  •