Adventures in Actionscript 3.0: Working with Microsoft Access

If there’s one thing I really care about for readers and followers is that you don’t just take in what I’m doing, but also “why” I’m doing it. Context is everything, so in this post will walk you through some code, but I’m really trying to let you into my head so you might understand how I model abstract problems with tools most everyone can grasp. Hopefully, as you follow along my trail, you’ll be able to pick up something useful to apply in your work.

In December, I was presented with a knowledge management problem for my real job as an internal consultant. Like many large organizations, we have some curricula established for career advancement, but this information is scattered all over the company intranet, which is usually littered with a lot of irrelevant or outdated materials. I was asked to come up with a way for our employees to easily find out what courses they need to register for in the LMS in the learning map they wish to follow — and to have this information available for a number of learning maps in one location.

Modeling in Microsoft Access

The old way of organizing learning maps for our employees was for one HR employee to update a bunch of separate powerpoint decks, one tied to each learning map. They were formatted similarly, but the first thing I thought about was coming up with a model to fit each learning map in so they were using the same taxonomy (category names and labels) and the same structure.

The powerpoint decks illustrated learning maps that at a high level look like this:

access_as3_project_learning_map_deck.png

  1. There are a bunch of courses lumped together in a couple of high level categories, which I decided to call “Tracks.” The reason for calling them “Tracks” is because each of the courses also had their own category — some are product courses, some are compliance courses, some are technology courses, etc. A Track might pull on courses from multiple categories. That implied some kind of distinction, if not some kind of hierarchy — and that different way of looking at a collection of courses an employee might need to take meant that I needed to have separate ways (at least two) of organizing these courses in one learning map.
  2. There are several learning maps that I have documentation for (who knows how many might be out there that I don’t know about). Each learning map could potentially pull some of the same courses, so that also implied a need to have yet another, abstracted way of organizing courses separate from the learning maps.
  3. In some of the powerpoint decks, there were internal links being referenced and aggregated in some of the tracks. Some tracks even pulled in another learning map! To me, this implied that they type of resource had to be distinct from the resource itself, because sometimes a course is part of a learning map. Sometimes a course may be aggregated with a learning map. And a learning map might consist of a collection of courses, links and learning maps.

Confused? Hopefully not. After stewing over a couple of these decks, I very quickly came up with a relational database structure that looks exactly like this:

access_as3_project_db_relationships.png

Here’s how it actually plays out. In slides 2, 8, 14, 21 and 28 in the above (almost unreadable) slide deck, you see a bunch of a boxes in columns. Each column potentially represents a category, but the aggregation of columns on a single slide represents a track. Once I could discern a repeatable logic from this model and found it applicable to the other powerpoint decks, the rest of the database came easy.

You notice in that high level view of the slide deck the three different colors (red, blue and that mustard)? Each color represents the availability of the given resource. Red represented content that was already available through the LMS. Mustard represented content that could be reserved, and blue represented content that was not available at the moment. This indicated to me that I needed to provide some way for people to know the availability of a given piece of content.

Each resource has a name, a type (intranet link or web course or learning map), an availability status, a link associated with it to launch the resource and some description information. All this information should stay consistent about the resource no matter what other aggregation, or collection of resources, the one may be part of later on. This way, if this particular resource needs to change (say it’s a course like Business Conduct Guidelines for Mammals), we only change the information in this one listing, and the change is absorbed in every instance of the resource.

Notice in the relationships diagram inside of Microsoft Access (shown above), the tables for Categories, Availability and Types are abstracted from the Resources? I did that because somewhere along the line I may have a need to add or combine how I categorize my resources. Perhaps I’ll have more types of resources to work with later on (virtual rooms, might be a new resource in the future). I may need tiers of availability status instead of just the available, on request or not available. In any of these cases, these are things that apply to all resources, potentially — I don’t want to make a change to one of these on potentially hundreds or thousands of entries. So we associate each label in these tables with its own identifier, and use that identifier in the Resources table.

Now the Tracks table works the same way — I have a numeric identifier that’s a primary key and I have a label associated with it. Why did I not include the Track with a resource like I did the Category? Well, a couple of reasons. First, there was a hierarchy implied (as I wrote about earlier), so I know that Tracks are higher up the chain of taxonomy than Categories. Secondly, I know that I can have multiple types of Resources, and potentially a Resource like a course for Social Skills for Muppets might be part of some kind of Soft Skills Track in one curriculum or learning map, but it might be part of a Management track in a different learning map. This assumption adds no real extra work if it turns out that a course is always used in the same Track universally, but it saves a lot of work in maintenance if the assumption proves correct as our curriculum mapping builds.

But this means we need to associate a Resource with a Track in yet another table — the Aggregations table. In this one table, I put together every learning map, because learning maps are how we’re aggregating our resources in the context of tracks. So each row in the Aggregations table has it’s own numeric identifier, or primary key, the identifier for a LearningRoadmapId which is tied to a resourceId from the Resources table (remember that Resources, as I defined them could be Learning Maps themselves); this means I’m using “LearningRoadmapId” as a parent identifier in the same table. I have another field listing the individual resource I’m mapping to the parent resource, and that maps again, to a ResourceId in the Resources table; this represents the actual child in how a resource might be part of another resource. That specific resource’s trackId is provided from the Tracks table. And, we ever need to specify an order to how learners should experience these resources, I put in an extra field for cardinality which would allow me to custom sort by LearningRoadmapId and then by cardinality.

My next posts will be on Making the Data Available to Flash and Manipulating XML Data in Flash.