Results 1 to 8 of 8
  1. #1
    TheRealMcGee is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    5

    Hierarchical information tracking for a school curriculum - What type of database am I after?

    I run a small coaching college with a bespoke curriculum that I have developed over the last ten years. The teaching and delivery method is fairly formulaic and uses a ‘knowledge tracker’ (Excel spreadsheets) to keep a track of students’ progress over the year. The approach itself is fairly simple – basically it’s a hierarchical approach of learning small pieces of information which cascade up into broader tasks which cascade up into an overall goal. A student might have ten goals over the year; Goal 1 is broken down into ten tasks; these ten tasks are further broken down into five to ten small objectives. Over the year lessons are are tied to these third order objectives with the related task in mind. Stats are kept on each objective and task, so if a student is struggling later on we can go back and see where his or her weaknesses were and target those. Near the end of the semester these tasks are consolidated, and the final assessments test the students on the overarching goals. As I said, we using Excel spreadsheets at the moment – one for each student which looks like this:

    1 Goal
    1.1 Task (with progress and achievement stats as metadata)
    1.1.1 Objective (with progress and achievement stats as metadata)
    1.1.2 Objective
    1.1.3 Objective


    1.2 Task
    1.2.1 Objective
    1.2.2 Objective
    2 Goal
    2.1 Task
    2.1.1 Objective
    2.1.2 Objective
    and so on…

    There’s nothing magical about this approach – it’s the content that we’ve developed and the way we track progress which is where our success lies. But as I mentioned, we’re using Excel which is becoming increasingly clunky and tedious, and I’d like to explore the option of developing an Access database. This way when it comes to the end of the semester with the push of a button we can query the database for all 85 of our students and bring up report which details their weakest ‘objectives’ and ‘tasks’ and target those (currently we do this in Excel but it takes significant time).

    I dabbled in databases years ago and have a general idea about relational tables. I can see with our curriculum we should have a table for all Goals, a different table for all Tasks each linked to their parent Goal through primary and foreign keys, and one for all the Objectives, again linked through keys to the parent Task, as well as a Table for the students. However, I imagine the general concept and framework of this kind of database isn’t exactly unique - after all it's just hierarchical information - so I’m wondering if this particular kind of database has a name and whether there are guide out there on how to build the general structure?

    Many thanks.
    Last edited by TheRealMcGee; 07-27-2019 at 06:22 PM. Reason: Paragraphs disappeared for some reason

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have a large commercial database for schools that does all the above and much more. If you are interested, its available at http://www.mendipdatasystems.co.uk/s...ser/4584605482. I'm mentioning it mint because a free DEMO version is available which you could look at and get some ideas. It includes a full set of dummy data for a fictitious school so its a huge download
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    TheRealMcGee is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    5
    Thanks isladogs, I had a quick look, it's quite in depth as you suggested. My Access skills are still very basic but I'll revisit it in the near future when I can figure things out a bit more!

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its a very fully featured application that covers almost all schools need in terms of data management and analysis.
    As its a commercial app, the DEMO version is restricted in that you can't view the code.
    Whilst I'd of course be happy to sell a site licence to you, its fairly expensive.
    The purpose in mentioning it was to hopefully give you some ideas.
    Good luck
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would think you would only need to relate the objectives, tasks and goals to one another if you have a standard format that will help you 'quick start' when a new student enters your program.

    For instance
    Goal 1 will *always* consist of Task A, B and C, further Task A will always consist of objective 1,2 and 3, Task B will always consist of objective 4, 5 and 6 and Task C will always consist of objective 7, 8 and 9.

    Even if it will 'almost always' consist of a standard structure for each goal and you want to eliminate some of the tasks and/or objectives it might be worth setting up your structure ahead of time.

    This becomes a bit more complex if some of the objectives can all in multiple tasks etc but still manageable.

    To answer your main question, I do not know if there is nomenclature for this type of structure (something like 'tree' or 'tree view' structure maybe?) but it's not hard to put together. I didn't download isladog's application so I'm not dismissing it, you just seem to have a very specific small need, that's all I'm addressing.

    If your GOAL level items have a very specific structure you follow all the time that makes it easier. If it's a negotiation between the student and your program about what they want to learn that's something else entirely.

  6. #6
    TheRealMcGee is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    5
    rpeare: Correct, the GOAL level items have a very specific structure and the underlying TASKS and OBJECTIVES do not cross over into other goals. I've done a bit more looking around the internet and "nested set" seems to be the word that pops up the most.

    A question on primary keys...our text books use that same indexing style that I used in my original post, ie: Curriculum A's text book is structured:
    Curriculum A, 1. Introduction to Goal 1
    Curriculum A, 1.1 Explanation of Objective 1.1
    Curriculum A, 1.1.1 Task one an exercises
    Curriculum A, 1.1.2 Task two and exercises
    Curriculum A, 1.1.3 Task three and exercises
    Curriculum A, 1.2 Explanation of Objective 1.2
    and so on

    A question on primary keys:
    The tables will consist of a field matching the text book chapter index, a small descriptor field and a value field (indicating what the test score is out of). I'm thinking of making the primary key the index field, eg "B 3.2.4" is the primary key for "Objective 4 under Task 2 under Goal 3 within Curriculum B". However, I would like it to be auto-calculated/generated as we enter all the data, so that when we're filling in all the data the objectives within task "B 3.2" it autopopulates as we move along. Is this doable? Even if it is doable, is it wise to manage the primary keys in this way?
    Last edited by TheRealMcGee; 07-29-2019 at 03:20 PM. Reason: Clarity

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    your primary key should be a value that isn't relevant to the data, most people use the autonumber for that. I use autonumber for *all* my primary keys. Your unique key could be a combination of four fields (curriculum, goal, task and objective) You do not want to stack those all into a single field in my opinion, because then you have to rip them apart if you want to do anything with the individual segments. Further if you try to sort something like

    A.1.1.1
    A.1.1.2
    ...
    A.1.1.10

    as a text field A.1.1.10 will come directly after A.1.1.1 which is not what you want.

    it sounds to me like your curriculum is static and if that is the case I would set up a 'dummy' or 'default' for each curriculum. Then when you attach a student to a curriculum it would copy the 'dummy' version of the selected curriculum as your preset. Then it'd just be a matter of editing the start date/end date and adding a test score, possibly notes for each step of the curriculum if necessary. you mentioned metrics but didn't give specifics but honestly with a good setup you could compare an individual student to any other student who had taken the curriculum or compare against averages, standard deviations, etc. I really haven't see anything I *couldn't* do in MS access other than some really hard core statistical analysis.

  8. #8
    TheRealMcGee is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    5
    Thanks rpeare, sounds like using autonumber for all primary keys is the safer option.

    Yes, the curriculum is static - it hasn't been updated in three years and we don't have the resources to update it any time soon. In any case, it working as it needs to. I still would like to have the index method of the text books reflected in the tables somehow, just not as primary keys.

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-20-2019, 02:18 PM
  2. Help with School Database
    By albus221 in forum Database Design
    Replies: 2
    Last Post: 09-07-2017, 03:50 PM
  3. Replies: 2
    Last Post: 10-28-2014, 11:41 AM
  4. School Transportation Database
    By SMOORELCC1 in forum Database Design
    Replies: 1
    Last Post: 01-18-2013, 12:43 AM
  5. School Enrollment Database
    By jpepin in forum Database Design
    Replies: 1
    Last Post: 04-08-2010, 05:23 PM

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
  •  
Other Forums: Microsoft Office Forums