Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012

    Unanswered: Serious Design help

    ok...I'm in a bit of a pickle...I'm currently a student on an internship at a big company and I've been entrusted with creating a database which has some serious redundant data issues...

    This is a database to create a standard for I.T disciplines, each having its own sub-discipline, and each having its own level (level 3, 4, 5, 6) and each level have its own number of competencies, and each competency has its own number of skills that you need to have in order to achieve said competency.

    Broken down like this so far:

    Discipline one-to-many Sub-Discipline

    Sub-Discipline one-to-many Level

    Level many-to-many Competency

    Competency one-to-many Knowledge

    Competency one-to-many Understanding

    Competency one-to-many Performance

    I have no idea how to create a database with no redundant data when you have so many might be out of a 1st year Bsc Student's leauge....but I love the challenge.

    the tables so far:

    Discipline table
    Discipline, Sub-Discipline 1, Sub-Discipline 2, etc..(depends on discipline)

    Level tables:

    Level 3
    Sub-Discipline1, level 3, Competencies

    Level 4
    Sub-Discipline1, level 4, Competencies

    Level 5
    Sub-Discipline1, level 5, Competencies

    Level 6
    Sub-Discipline1, level 6, Competencies

    (Redundant data problem - table would have many level 6 fields to cater for the ammount of competencies. I.e, if you have 5 competencies for level 6, you would have 5 repeated fields of sub-discipline1 and level 6)

    Competencies table:

    Competency, Understanding, Standard Code

    Competency, Knowledge, Standard Code

    Competency, Performance, Standard Code

    (The competencies table is the worst, you have different ammount of competencies with different ammounts of criterias for understanding, knowledge and peformance, so I tried creating tables for each competency, but the competency field will be repeated to cater for the ammount of understanding, knowledge and performance criterias)

    Also not included in the design is the issue of standard codes, sub-codes and sub-sub codes.
    I.e: a Sub-Discipline has a code of 401
    a competency has a code of 401.13
    a criteria (understanding/knowledge/performance) has a code of 401.13K

    I don't think a database is feasible at this point...or just way out of my leauge.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    for your discipline-to-competency hierarchy, use a single table, like this -- | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Also look into intersection tables, which will eliminate the need for any sub-discipline fields in the discipline table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Jul 2012
    thanks guys you've been a great 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