How do I build database to organize my learning goals and lesson plans?
January 15, 2015 9:45 AM   Subscribe

I need to organize my lesson plans and learning goals. I have a list of learning goals. I have a list of "activities," each of which addresses one or more learning goals. I want to be able to switch easily between listing activities chronologically and grouping the activities by learning goals. What miracle software should I be using?

I want to be able to look at a list of learning goals arranged in some sort of logical order (a multi-level outline would be best), and for each learning goal, see a list of the activities that address that learning goal. I want to also want to be able to arrange the activities, including worksheets, lectures, homework questions, quiz questions and exam questions in a lesson plan organized by day and weeks, sorted into a daily reading quiz, in-class activities, weekly quizzes and homework assignments, and exams. I want to be able to switch easily back and forth between these views.

Right now, I'm keeping two separate documents (one with activities listed by learning goal and one of daily lesson plans) but updating and editing is a disaster.

If I make any changes (e.g. to the phrasing of a learning goal or to the date of an activity) I want to make that change one place and have it propagate automatically. I want to be able to easily change the order in which the learning goals are listed, and easily move activities from one day to another. I don't fear the command line or editing text files, but drag-and-drop would sure be nice.

In my mental metaphor, each activity is written on an index card (ideally including some freeform notes). I want to be able to place those index cards in chronological order, but I also want to sort them into stacks by learning goal. This is where the physical model breaks down, because each activity might have multiple learning goals, so I'd have to have multiple cards for each activity. But this thing seems like the sort of thing a computer should be able to do extremely well!

Things I've tried that I haven't been able to make work:

TiddlyWiki seemed like exactly what I needed, right down to the index-card metaphor, and I've used it with great success for simple notetaking. However, just "tagging" activity-tiddlers with learning goals isn't flexible enough, and I needed to work out how to sort things by date, but the online documentation is all over the place. It seems to jump from "here are the basics" to "you are proficient in javascript [I am not] and steeped in the eccentric vocabulary of TiddlyWikis" without much in between. And there's a LOT of documentation, but for all the different versions, and the stuff I want to do seems to be highly version-dependent. Also TiddlyWikis are too vulnerable to file munging, and I'm also terrified of putting a HUGE amount of work into it and then find that it's broken by some browser update and it never gets fixed.

Excel doesn't do blocks of verbose text well, doesn't gracefully handle tagging an activity with multiple learning goals. Fundamentally, I feel like I need a spreadsheet with a third dimension. I'm also not an Excel expert, so if there's some clever way to do what I want to do in Excel, let me know. (I thought pivot tables would be the answer, but they seem only able to count and add things, not display text?)

I use a Mac. I'm comfortable at the command line and willing to do some programming, but I don't want this to turn into a second-career software development job. I'm willing to pay a few tens of dollars but not hundreds of dollars for software packages. I don't have any need to share with anyone else, and I'd just as soon have something that's just on my hard drive. I do want to be able to use this for years and years, so I fear hinky fly-by-night cloud services that are liable to disappear.

I think maybe I need a database, but I haven't worked with databases extensively. The simple options I've looked at seemed too simple and clearly couldn't do my two different views, and I don't know enough about databases to even know what features I'm looking for in a more complicated package. I think a "personal" database is what I want, but the ones I'm looking at seem more oriented toward pretty color schemes and ease of use than the kind of sorting/resorting I want to do, and, while they are within my price range, and, frustratingly, none of them have free trials, and I don't want to drop a bunch of money on things that don't work for me.

Do you have extensive experience with some software does what I want? Or can you suggest a metaphor-flip that will make this solvable using Excel or something simple?
posted by BrashTech to Computers & Internet (9 answers total) 10 users marked this as a favorite
Microsoft ACCESS would do this. But costs. I think the term you're looking for is "relational database"
posted by vitabellosi at 10:20 AM on January 15, 2015

Best answer: If you're looking to simplify this somewhat, I LOVE Trello.
I've used it for years and tend to think about it like index cards on steroids.
It's original use was to be an online Kanban Board, but it's SO much more than that.
Once you get the hang of it (takes about 5 minutes or less), you'll wonder how you lived without it.
Online, Android and iPhone app, so it can follow you around.
posted by John Kennedy Toole Box at 10:45 AM on January 15, 2015

Yeah, if you want to apply the most general solution to the problem, it's probably a relational database. Something like MySQL is free and extremely powerful, but you're going to have to learn a whole language, and I always find dealing with databases ... tedious. This is overkill.

I haven't tried Trello , but I've heard some good things about it.

But wait...

Can you suggest a metaphor-flip that will make this solvable using Excel or something simple?

How about a giant sheet of paper (conceptually), with weeks running down the Y axis, learning goals running across the top X axis, and colored dots designating each activity? If activity Red is going to happen in weeks 3 and 5, and address learning goals B, C, and F, put red pins in row 3 and 5 , columns B, C, F. What activities addressed goal C? Every colored pin in C column. Which goals were addressed in week 4? Look at row 4 and see which columns had pins in them.

Does this work? If so, you can implement it in Excel with some colored cells, I think...?
posted by RedOrGreen at 1:42 PM on January 15, 2015

If feel like this is something that could be done with excel - assuming there were a reasonable number of learning goals. Column for Description - cell set to wordwrap to handle the massive block of text bit, column for lesson date and then a column for each learning goal. If a particular lesson applies to that learning goal, the cell under the column gets an "X" (or yes, or 1 or whatever). If it doesn't leave it blank.

Sort by date to see them in date order, sort by goal 1, then goal 2, ... goal N to see them as they apply to a particular goal. As I'm typing this, it occurs to me that a single lesson won't appear more than once so it will sort under whatever goal is first in the list. I likely wouldn't try to solve that unless it happened a ton and I really needed to see the lesson twice. If I decided I did, I could probably solve the problem with VBA. Maybe auto create a tab for every learn target and then write VBA that copies all rows from the main sheet to a tab if the tab's title is included in the "tags" column.

Of course, a database like MySQL and a web language would solve but man... really heavy. Access with it's built in forms and reports could do it but does cost. OpenOffice - the MS Office like open source alternative has a version of Access called "Base". I've never tried it but it might do the trick.
posted by grimtheelder at 2:20 PM on January 15, 2015

I feel like I need a spreadsheet with a third dimension
That's a good reason to use a database, and a good description of what a query does to two tables, adds a dimension.
If I was putting this into a database, I'd have two or three object tables and two relational tables:
-all the Activities with their short name and a more verbose description (each one gets an ID number)
-all of the learning Goals (each one gets a different ID number)
-all valid Dates (if this is a recurring lesson plan with on and off days)
-one table lists which Activities go with which Goals, using the ID numbers so you can easily change names and descriptions
-one table that assigns each Activity to a Date, again using ID numbers for the Activites

You could do that in Excel if you got really good at using the Vlookup formula to pull the Dates and Goals into the activities sheet. Then you work on pulling all of the Activities and Goals into the Dates sheet. Keeping everything updated in Excel when changing a date is hard and can use a lot of memory. That is why people switch to Access or another database. If you have Office, you might already have Access, so it is worth checking. If not, LibreOffice has a database program that would probably be similar enough to help you decide if you want to go down that road.
posted by soelo at 2:21 PM on January 15, 2015

Response by poster: I was very excited about Trello for a moment. The Calendar is exactly what I needed, and there are a lot of features that would be really handy—but, if only they had free tagging instead of a maximum of six labels! Or the ability for cards to appear on multiple lists. (They say that free tagging has a lot of interest from the user base but the idea is currently on hold while they work on other things.)

I have considered and rejected the huge 2-D grid in the past... I have an unreasonable number of learning goals, I'm afraid, more than 100. (Never let it be said that my students don't get a good value for their tuition dollar.)

I'll look into Vlookup. I think mySQL is, yeah, too much. We have a campus license for MSOffice but I don't currently have Access installed. I can check with IT to see if we indeed actually have a license for that.

Thanks to everyone for your suggestions!
posted by BrashTech at 2:53 PM on January 15, 2015 [1 favorite]

Best answer: Looks like Trello has unlimited labels as of November...
posted by lemonade at 8:05 PM on January 15, 2015

Response by poster: Wow, that's great, lemonade! I guess I stumbled across some out-of-date documentation!

Trello it is! Thanks again!
posted by BrashTech at 11:03 AM on January 16, 2015

Response by poster: Here is the result!

I'm happy to talk to specifics by MeMail if anybody is curious.
posted by BrashTech at 10:19 AM on February 11, 2015 [1 favorite]

« Older Excel Question: VLOOKUP and beyond?   |   No-prep veggies Newer »
This thread is closed to new comments.