Tuesday, February 18, 2014

UX for Learning: Design Guidelines for the Learner Experience

A post by Dorian Peters @ http://uxmatters.com/mt/archives/2012/07/ux-for-learning-design-guidelines-for-the-learner-experience.php

UX for Learning: Design Guidelines for the Learner Experience

Published: July 24, 2012
“With educational applications for kids, corporate eLearning, and online degree programs, more and more UX designers face design briefs for creating digital experiences with an educational purpose.”
With educational applications for kids, corporate eLearning, and online degree programs, more and more UX designers face design briefs for creating digital experiences with an educational purpose. Other applications, whether they’re new or launching new features, often present micro-learning experiences that gently teach users how to use the software.
But as UX designers, how much do we really know about how people learn? And does design really affect learning?
Absolutely. Research shows that user interface and interaction design decisions significantly affect how well users learn. For a manifesto on that topic, see my article “Say Hello to Learning Interface Design.”Nevertheless, for the most part, we go into these projects armed with a wealth of UX knowledge about how people use technology, but without any sound knowledge about how people learn.
In this article, I’ll begin to fill that gap by presenting some design guidelines that derive from key findings from relevant psychology and education research on learning with technology. These findings relate specifically to user interface and interaction design for digital learning experiences. I’ve drawn most of these guidelines from the pioneering work of educational psychologist Richard E. Mayer, whose discoveries form the foundation of much multimedia instruction today.

What Is Learning Experience Design (LXD)?

“A big part of LXD is designing a user interface in a way that supports and enhances the cognitive and affective processes that learning involves.”
In much of design, we aim to craft a user experience that meets business or communication goals. In learning experience design, the goal is to help someone learn something. It’s about improving learning outcomes and the quality of the learning experience. A big part of LXD is designing a user interface in a way that supports and enhances the cognitive and affective processes that learning involves.

A View of the Landscape: Examples of Digital Learning

Digital user interfaces for learning include digital environments, technologies, and tools. Specific examples include:
  • learning objects—These are self-contained experiences based on just one learning objective. Users can use them as needed or combine them for a more complete course. You’ll find a slew of these at The Learning Federation.
  • educational games and simulations—Computer games designed around educational goals—like Global Conflicts and the NASA gameMoonbase Alpha—are called serious games.
  • Learning Management Systems (LMS)—These large software systems manage tens or even thousands of courses and students. They provide tools for course portals, assessment, and synchronous and asynchronous interactions. Examples include Blackboard, Coursera, shown in Figure 1, and the open-source LMS Moodle.
  • synchronous collaborative learning spaces—Virtual spaces in which learners get together in real time. These might include video, chat, shared whiteboards, note taking, and wikis. Two examples are Adobe Connect, shown in Figure 2, and KnowledgeForum.
  • Intelligent Tutoring Systems (ITS)—These computer systems provide customized feedback to students without human intervention—for example, AutoTutor, shown in Figure 3, and DeepTutor.
  • corporate eLearning courseware—Companies deliver courses via digital technology to teach their employees workplace skills like how to use software or soft skills such as customer service or sales strategy. Examples include Savv-e, shown in Figure 4, and ClearlyTrained.
  • 3D Virtual Learning Environments (VLEs)—Purpose-built, 3D worlds like JumpStart and Virtual Singapora, shown in Figure 5, structure learning. Learning of all types of subjects—from math to drama—also takes place in off-the-shelf worlds like Second Life.
  • mobile learning applications—There is a list of mobile learning applications for children at “Smart Apps for Kids.” Figure 5 shows an example, PBS Kids.
Figure 1—Coursera
Coursera
Figure 2—Adobe Connect
Adobe Connect
Figure 3—AutoTutor
AutoTutor
Figure 4—Savv-e
Savv-e
Figure 5—Virtual Singapora
Virtual Singapora
Figure 6—PBS Kids mobile learning applications
PBS Kids mobile learning applications

Guidelines for the Design of Learning Interfaces

“Extraneous visuals are especially problematic in regard to learning. … Extraneous imagery adds to cognitive load.”
Here is a summary of guidelines that relate specifically to user interface design from the larger set of findings by Richard E. Mayer.

1. The multimedia principle: People learn better from a combination of text and visuals rather than text alone.

This is not exactly breaking news for visual designers, but it’s important to know that the inclusion of visuals is not just nice; it improves learning. Visuals might include illustrations, photos, infographics, diagrams, or comparative charts, as well as videos or animations. Text refers to both written text and audio. Having said this, I must note that it’s necessary to design combinations of text and visuals properly to yield learning advantages.

2. Graphics should be relevant, not merely decorative.

Extraneous visuals are especially problematic in regard to learning. While it might be tempting to load up a lesson on global warming with various photos of natural wonders to enhance its visual appeal, extraneous imagery adds to cognitive load. Whatever portion of users’ brain power goes into processing these photos is unavailable for learning. This doesn’t mean a user interface can’t be beautiful. But find ways of guiding visual focus to what matters and lean toward minimalism when in doubt.
Mayer’s findings refer not to graphic user interface (GUI) design, but to graphics that serve as an adjunct to text—such as images that accompany the text of a lesson. In this case, steer away from decorative or representational graphics—for example, generic stock photos—and instead focus on providing explanatory graphics that help the learner process and interpret information, including:
  • relational graphics—like comparative graphs or interactive systems models
  • graphic organizers—which structure the concepts a lesson’s text discusses
  • transitional graphics—which show change over time
  • graphics showing things that would otherwise be invisible—for example, an atomic structure, greenhouse gas flow, or the global movement of money
  • graphics that help place learning in context—for example, a GUI metaphor or virtual world

3. Use animations to teach physical procedures and still images to teach processes.

“People learn physical procedures … better by watching videos or animations. In contrast, they learn conceptual processes … better when they are illustrated by a series of static images.”
People learn physical procedures—such as how to fold origami, assemble a piece of furniture, or tie a square knot—better by watching videos or animations. In contrast, they learn conceptual processes—for example, how rain forms—better when they are illustrated by a series of static images. Apparently, this is because, from early in human evolution, we’ve been hardwired to learn motor skills by observation. The understanding of complex processes, however, is not quite so primal. In this case, an animation does too much of the work for us and removes pacing out of our control, so we become passive viewers. In contrast, a series of static images requires us to make logical connections between them, which helps us to learn, and we can do this at our own pace.

4. To promote understanding, simplify visuals or make them abstract.

People learn processes and principles better from simplified visuals. For example, simple line drawings that illustrate a process such as cellular mitosis are generally better than a series of photos showing the real thing in microscopic imagery. A computer animation that omits extraneous background detail is simpler than a video that shows everything. However, there are likely to be some exceptions—for example, advanced practice—that call for highly realistic simulations such as in aircraft simulators.

5. Don’t separate related text and visuals.

In line with the basic design principle of proximity, learning research has shown that people learn better when text relating to an image appears near that image. As obvious as it sounds, designers often violate this guideline. For example, rather than adding a list of labels to a diagram’s caption, create callouts, drawing lines to various parts of the diagram and labeling those parts directly; and rather than providing feedback for an incorrect answer at the bottom of a screen, include it beside the learner’s answer.
Also, include an explanation in the form of a narration whenever possible. If you can’t use narration, provide text for learners to readbefore playing a video. While you can present these on the same screen, avoid having text appear on a screen at the same time a video or animation is running. That would be too much visual information for learners to take in at once.

6. Use virtual agents to deliver instructional content.

“People learn better when they perceive social presence.”
Research shows that people learn better when they perceive social presence. For this reason, using a conversational tone in first or second person increases learning. We are hardwired to pay more active attention when we’re dealing with human beings rather than machines. This is probably why virtual coaches, tutors, characters, or avatars help people learn. Keep in mind that you should use agents for instruction, notentertainment. Agents are good for providing hints, explanations, and demonstrations, but you need to use them sparingly to prevent their becoming irritating.
Interestingly, realistic characters are not more effective instructors than cartoon characters. In fact, an agent does not even need to be visible, as long as learners hear a voice. Also, a voice should be a human voice rather than a machine voice. So don’t blow your budget on a 3D animated avatar with a robotic voice. A simple illustration with a human-voice narration—regardless of whether that human is visible—is enough for learning gains. As a final note, while having a realistic human appearanceis not necessary, learners do benefit from agents who exhibit realistic human behavior such as human-like eye gaze and gesturing.

7. Break complex information into chunks and provide pre-training.

Two basic ways of managing complex information are either to break it into segments, or chunks, or to extract difficult terms or key concepts and deal with those first, which is called pre-trainingSegmenting means breaking content into parts that deal with just one, two, or three steps or concepts at a time.
Lynda.com provides a wonderful library of training courses on everything that relates to the Web. If you haven’t already seen the site, it’s well worth a visit. Their instructional approach is to provide high-quality videos of best practices or software training. This works well because video is a good medium for demonstrating procedures. They manage complexity, and give users control over the pacing, by breaking each course up into bite-sized concept chunks. This also means people can select and review just the bits they need when they need them.

8. Avoid irrelevant video, audio, stories, and text.

Research shows that adding sidebar stories for the sake of interest hurts learning. It may seem sensible to spice up a lesson on the physics of lightning with a sidebar on the world-record holder for the most lightning hits. However, research has proven that this is not a good idea. The message here is to focus on what is inherently interesting about the content rather than adding extraneous detail—especially if specific learning goals are at stake. Along these lines, adding music or audio to learning experiences for ambiance impairs learning.

9. Set learning in a real-world context to facilitate knowledge transfer.

“The closer a learning experience is to a real-world context, the more likely learners will be able to transfer what they’ve learned to the real world.”
The closer a learning experience is to a real-world context, the more likely learners will be able to transfer what they’ve learned to the real world. One criticism of video games for learning stems from their low transfer rates—skills people learn in the game world don’t transfer to the real world. One company thought a corporate orientation game set in a sci-fi world would have greater appeal. However, in reality, it had little uptake because learners couldn’t see its relevance. Moreover, learners would be unlikely to transfer concepts they learned in such an environment back to the real world, because there would be no contextual cues embedded in the learning.
If people need to adapt the skills they’re learning to multiple environments and diverse circumstances, include examples set in more than one context. When you expose learners to multiple contexts, they are better able to extract essential principles and transfer them to various real-world scenarios.

10. Provide informative feedback.

Avoid providing feedback that sounds personal or draws attention to the self instead of the task—for example, “good job” or “you got that wrong.” Also, avoid giving feedback that is uninformative like “correct” or “incorrect.” While an instructional designer writes the feedback, as a user interface designer, you need to design space in the interface for rich forms of feedback, remembering to place answers near the questions to which they refer.

11. Choose your tools based on learning activities.

Different types of tools are most effective for different types of learning activities. For example, asynchronous communication tools that allow interaction over time—such as discussion forums—are well suited to learning experiences that require reflection or independent research. Synchronous tools like chat or live online classroom sessions are well suited to experiences that benefit from group synergy and social presence.

12. Know when to give learners control.

“Allow learners to control their pace through a learning experience. Give them video and animation controls, let them review previous content, and allow them to exit at any time.”
Allow learners to control their pace through a learning experience. Give them video and animation controls, let them review previous content, and allow them to exit at any time. However, you should be more careful about giving learners control of the sequence of instruction.
Research shows that novices are not able to effectively design their own path through non-linear content or decide how much practice they require. This impacts how you design both navigation and information architecture. You should allow learners choices regarding what topics to read or activities to do only if they have prior knowledge, in advanced courses, when chunks of content are not interdependent, or when designing a learning experience to be supportive and informational rather than for skill building. Along these lines, research shows that unguided exploratory learning is not effective for achieving specific learning outcomes.

13. Design learning games and simulations in strict alignment with learning goals.

The recent trend in gamification has led to the assumption that adding badges, rewards, and scoreboards to just about anything makes it a game. The main point to take away from games and learning is the notion of extrinsic versus intrinsic motivation. While it’s tempting to think that adding game rewards and goals would increase learners’ motivation and thus help them to learn better, you have to ask what is motivation about? Are people more motivated by learning a topic or by collecting points?
In fact, research has shown that learning does not improve when a learner’s focus is on game goals rather than learning goals. You could have learners practice and teach them facts by placing them in a virtual Jeopardy-style game show, but this would decontextualize the knowledge making it unlikely to transfer. Effective learning games retain contextual cues, align game goals with learning goals, and drive interest with intrinsic motivation. More on that later.

14. Be aware of the expertise reversal effect.

“Experts are more flexible and can learn just as well from text alone as they can from a combination of text and visuals.”
A major caveat to everything I’ve said so far is that the rules break down when it comes to experts. This is known in the research world as theexpertise reversal effect. In essence, all of the guidelines I’ve provided are especially important for novices. If you’re teaching biology or management skills to non-experts, everything applies. However, if you’re teaching advanced concepts in biology to a doctor or management strategy to a CEO, these rules may not apply. Such design decisions usually make less impact on such people. Experts are more flexible and can learn just as well from text alone as they can from a combination of text and visuals. Experts don’t need information to be chunked, and they can handle having more control over their instruction.
In general, following design guidelines that benefit non-experts won’t negatively impact experts. However, in a few cases, the rules for designing for novices do actually decrease learning outcomes for experts. Therefore, if you’re designing for an audience of experts, it’s worth reading more about their unique requirements—first, in Clark and Mayer’s book; second, by looking up the research on the expertise reversal effect.

Unleash Intrinsic Motivation

“Motivation is key in learning.”
Motivation is key in learning. Therefore, when considering approaches to digital learning, it’s worth giving motivation some extra attention. In addition to the points I made earlier about the pitfalls of gamification and the limits of extrinsic motivation, I want to focus now on how UX professionals can become more creative about uncovering intrinsic motivation.
Too often, intrinsic motivation gets skimmed over in digital learning design, but UX professionals are just the right people to tackle this. It takes empathy, ideation, and a knack for storytelling. One prevalent example is kids and literacy. The market abounds with drill and practice software for spelling and phonics. But imposing extrinsic motivation in the form of points, competition, and speed rounds has obvious limitations. Can’t we do better?
We need to ask ourselves the question: what is inherently motivating about reading and writing? The answer? There are many motivations: storytelling, engagement with characters, self-expression. Kids get wildly excited by Harry Potter. They use social media to tell the stories of their own lives. How can we tap into this kind of motivation, which is so much more powerful than badges and scores, to help teach literacy skills? Might we place spelling words in stores instead of random sentences? Could we ask students to write a letter to Hogwarts rather than just fill in a template? Could we design collaborative, global story-writing activities so all children, even lower-ability writers, could be part of something special? Could we ask famous writers to contribute?
The point is, while the onus of activity design rests with teachers and instructional designers, there may be times when UX designers have an opportunity to contribute to effective learning. In these cases, challenge your team to go beyond extrinsic motivation and tap into the heart of intrinsic motivation—what intrinsically makes your topic exciting.

Going Deeper

As I’m sure you’ve guessed, there is a lot more to designing for learning than what I’ve presented here. The trouble is, much of what is known is buried in academic research. If designing for learning is a significant part of your job, here’s where to turn next to enrich your credentials:
  • Read the book. For a complete list of principles, examples, and in-depth explanations of the psychology behind them, read Mayer and Clark’s book E-learning and the Science of Instruction.
  • Follow my blog. I have been posting information on LXD on my blog,Learning Interface Design, regularly since 2005.
  • Read the research. Keep up with new publications on this research topic by following or joining the Learning Interface Design research-sharing community on Mendeley.

In Conclusion

These guidelines provide a solid start to informing our practice of the design of learning experiences. As digital learning continues to enrich and, in some cases, even displace traditional learning strategies, I anticipate that a rigorous field of research and best practices that focus specifically on the design of user interfaces and experiences for learning will begin to develop.

Friday, February 14, 2014

Solved by Flexbox

A post by Philip Walton @ http://philipwalton.github.io/solved-by-flexbox/

Introduction

CSS has been lacking proper layout mechanisms for far too long. Transitions, animations, filters, all of these are great and useful additions to the language, but they don't address the major problems that Web developers have been complaining about for what seems like an eternity.
Finally, thanks to Flexbox, we have a solution.
This site is not another CSS framework. Instead, its purpose is to showcase problems once hard or impossible to solve with CSS alone, now made trivially easy with Flexbox. And with the recent release of Internet Explorer 11 and Safari 6.1, the latest Flexbox spec is now supported in every modern browser.
Check out the demos below. View the styles in the Web inspector or dive into the source to see just how easy CSS layout will become once Flexbox becomes mainstream.

Showcase


  • Better, Simpler Grids

    Better, Simpler Grids


    Flexbox gives us most of the features we want from a grid system out of the box. And sizing and alignment are just one or two properties away.

  • Media Object

    The Media Object


    Create media objects with fixed or varying figure sizes without worrying about overflow, clearfixing, or block formatting context hacks.

  • Input Add-ons

    Input Add-ons


    Creating full-width, fluid input/button pairs has been impossible for most of the history of CSS. With Flexbox it couldn't be easier.

  • Sticky Footer

    Sticky Footer


    Getting your footer to stick to the bottom of sparsely contented pages has always been tricky. And if the footer's height is unknown, it's basically impossible. Not so anymore.

  • Holy Grail Layout

    Holy Grail Layout


    This classic problem has been challenging CSS hackers for years, yet none of the historical solutions have fully solved it. With Flexbox, it's finally possible.

  • Vertical Centering

    Vertical Centering


    A lack of proper vertical centering has been the butt of almost every CSS joke ever told. While it's technically always been mostly possible, it's never been very intuitive — until now.

Browser Support

  • Chrome
    21+
  • Opera
    12.1+
  • Firefox
    22+
  • Safari
    6.1+
  • IE
    11+

Caveats and Known Issues

  • IE 10 has Flexbox support but for a draft version of the current spec: (display:flexbox).
  • Safari 6 and earlier support the original Flexbox syntax, which is now obsolete: (display:box).
  • Firefox 27 and earlier do not support multi-line flexboxes. See this bug for more details.
  • For a full browser support comparison, check out caniuse.com/flexbox

About the Code

In addition to showcasing the power of Flexbox, this site also aims to be an example of modern CSS design and architectural practices. Namely modular, reusable style rules built on a predictable and testable naming convention.
All of the CSS code in these examples make use of the SUIT naming conventions developed by Nicolas Gallagherand are based on OOCSS and BEM methodologies. Each example includes one or more reuseable CSS components allowing you to adapt or copy these patterns in to your own projects. Links are provided to their respective components on each example page.
This site also uses the excellent autoprefixer library by Andrey Sitnik, which takes care of vendor prefixing the CSS, so you don't have to think about it.
If you find a mistake or would like to suggest an additional example, feel free to open an issue or submit a pull request on Github.

Multiple Simultaneous Ajax Requests (with one callback) in jQuery

A post by Chris Coyier @ http://css-tricks.com/multiple-simultaneous-ajax-requests-one-callback-jquery/

Multiple Simultaneous Ajax Requests (with one callback) in jQuery

PUBLISHED BY CHRIS COYIER
Let's say there is a feature on your website that only gets used 5% of the time. That feature requires some HTML, CSS, and JavaScript to work. So you decide that instead of having that HTML, CSS, and JavaScript on the page directly, you're going to Ajax that stuff in when the feature is about to be used.
We'll need to make three Ajax requests. Since we don't want to show anything to the user until the feature is ready to go (plus they all kinda rely on each other to work right) we need to wait for all three of them to be complete before proceeding.
What's the best way to do that?
Ajax calls in jQuery provide callbacks:
$.ajax({
  statusCode: {
    url: "/feature",
    success: function() {
      // Ajax success
    }
  }
});
Or the "Deferred" way, this time using a shorthand $.get() method:
$.get("/feature/").done(function() {
  // Ajax success
});
But we have three Ajax requests we're needing to perform, and we want to wait for all three of them to finish before doing anything, so it could get pretty gnarly in callback land:
// Get the HTML
$.get("/feature/", function(html) {

  // Get the CSS
  $.get("/assets/feature.css", function(css) {
    
    // Get the JavaScript
    $.getScript("/assets/feature.js", function() {

       // All is ready now, so...

       // Add CSS to page
       $("<style />").html(css).appendTo("head");

       // Add HTML to page
       $("body").append(html);

    });

  });

});
This successfully waits until everything is ready before adding anything to the page. So by the time the user sees anything, it's good to go. Perhaps that makes some of you feel nauseated, but I've done things that way before. At least it makes sense and works. The problem? It's slow.
One request ... wait to be done ... another request ... wait to be done ... another request ... wait to be done ... go.
It would be faster if we could do:
All three requests in parallel ... wait for all three to be done ... go.
We can use a bit of Deferred / Promises action to help here. I'm sure this is some JavaScript 101 stuff to some of you but this kind of thing eluded me for a long time and more complex Promises stuff still does.
In our simple use case, we can use jQuery's $.when() method, which takes a list of these "Deferred" objects (All jQuery Ajax methods return Deferred objects) and then provides a single callback.
$.when(

  // Deferred object (probably Ajax request),

  // Deferred object (probably Ajax request),

  // Deferred object (probably Ajax request)

}.then(function() {

  // All have been resolved (or rejected), do your thing

});
So our callback-hell can be rewritten like:
$.when(
  // Get the HTML
  $.get("/feature/", function(html) {
    globalStore.html = html;
  }),

  // Get the CSS
  $.get("/assets/feature.css", function(css) {
    globalStore.css = css;
  }),

  // Get the JS
  $.getScript("/assets/feature.js")

).then(function() {

  // All is ready now, so...

  // Add CSS to page
  $("<style />").html(globalStore.css).appendTo("head");

  // Add HTML to page
  $("body").append(globalStore.html);

});

Another use case: mustard cutting

My use-case example above is a 5% feature. Keep the page lighter for the 95% of users who don't use the feature, and have it be a relatively quick add-on for those that do.
Another situation might be a cut-the-mustard situation where you add in additional features or content to a page in certain situations, as you decide. Perhaps do amatchMedia test on some media queries and determine the device's screen and capabilities are such that you're going to include some extra modules. Cool, do it up with some parallel Ajax calls!

Thursday, February 6, 2014

Database Schema for Tagging System


Tags: Database schemas
Recently, on del.icio.us mailinglist, I asked the question “Does anyone know the database schema of del.icio.us?”. I got a few private responses so I wanted to share the knowledge with the world.

The Problem: You want to have a database schema where you can tag a bookmark (or a blog post or whatever) with as many tags as you want. Later then, you want to run queries to constrain the bookmarks to aunion or intersection of tags. You also want to exclude (say: minus) some tags from the search result.
Apparently there are three different solutions (Attention: If you are building a websites that allows users to tag, be sure to have a look at my performance tests as performance seems to be a problem on larger scaled sites.)

"MySQLicious" solution


mysqlicious sample datamysqlicious database stucture
In this solution, the schema has got just one table, it is denormalized.
I named this solution “MySQLicious solution” because MySQLiciousimports del.icio.us data into a table with this structure.

Intersection (AND)

Query for “search+webservice+semweb”:
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags LIKE "%semweb%"

Union (OR)

Query for “search|webservice|semweb”:
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
OR tags LIKE "%webservice%"
OR tags LIKE "%semweb%"

Minus

Query for “search+webservice-semweb”
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags NOT LIKE "%semweb%"

Conclusion

The advantages of this solution:

Disadvantages:
  • You have a limit on the number of tags per bookmark. Normally you use a 256byte field in your DB (VARCHAR). Otherwise, if you took a text field or similar, the query times would slow down, I suppose
  • Patrice noticed that LIKE "%search" will also find tags with “websearch”. If you alter the query to LIKE " %search% " you end up having a messy solution: You have to add a space to the beginning of the tags value to make this work.

"Scuttle" solution

Scuttle organizes its data in two tables. That table “scCategories” is the “tag”-table and has got a foreign key to the “bookmark”-table. database structure of scuttle

Intersection (AND)

Query for “bookmark+webservice+semweb”:
SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId
HAVING COUNT( b.bId )=3
First, all bookmark-tag combinations are searched, where the tag is “bookmark”, “webservice” or “semweb” (c.category IN ('bookmark', 'webservice', 'semweb')), then just the bookmarks that have got all three tags searched for are taken into account (HAVING COUNT(b.bId)=3).

Union (OR)

Query for “bookmark|webservice|semweb”:
Just leave out the HAVING clause and you have union:
SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId

Minus (Exclusion)

Query for “bookmark+webservice-semweb”, that is: bookmark AND webservice AND NOT semweb.
SELECT b. *
FROM scBookmarks b, scCategories c
WHERE b.bId = c.bId
AND (c.category IN ('bookmark', 'webservice'))
AND b.bId NOT
IN (SELECT b.bId FROM scBookmarks b, scCategories c WHERE b.bId = c.bId AND c.category = 'semweb')
GROUP BY b.bId
HAVING COUNT( b.bId ) =2
Leaving out the HAVING COUNT leads to the Query for “bookmark|webservice-semweb”.
Credits go to Rhomboid for helping me out with this query.

Conclusion

I guess the main advantage of this solution is that it is more normalized than the first solution, and that you can have unlimited number of tags per bookmark.

"Toxi" solution

image
Toxi came up with a three-table structure. Via the table “tagmap” the bookmarks and the tags are n-to-m related. Each tag can be used together with different bookmarks and vice versa. This DB-schema is also used by wordpress.
The queries are quite the same as in the “scuttle” solution.

Intersection (AND)

Query for “bookmark+webservice+semweb”
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3

Union (OR)

Query for “bookmark|webservice|semweb”
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id

Minus (Exclusion)

Query for “bookmark+webservice-semweb”, that is: bookmark AND webservice AND NOT semweb.

SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id
AND (t.name IN ('Programming', 'Algorithms'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python')
GROUP BY b.id
HAVING COUNT( b.id ) =2

Leaving out the HAVING COUNT leads to the Query for “bookmark|webservice-semweb”.
Credits go to Rhomboid for helping me out with this query.

Conclusion

The advantages of this solution:
  • You can save extra information on each tag (description, tag hierarchy, …)
  • This is the most normalized solution (that is, if you go for 3NF: take this one :-)
Disadvantages:
  • When altering or deleting bookmarks you can end up with tag-orphans.
If you want to have more complicated queries like (bookmarks OR bookmark) AND (webservice or WS) AND NOT (semweb or semanticweb) the queries tend to become very complicated. In these cases I suggest the following query/computation process:
  1. Run a query for each tag appearing in your “tag-query”: SELECT b.id FROM tagmap bt, bookmark b, tag t WHERE bt.tag_id = t.tag_id AND b.id = bt.bookmark_id AND t.name = "semweb"
  2. Put each id-set from the result into an array (that is: in your favourite coding language). You could cache this arrays if you want..
  3. Constrain the arrays with union or intersection or whatever.
In this way, you can also do queries like (del.icio.us|delicious)+(semweb|semantic_web)-search. This type of queries (that is: the brackets) cannot be done by using the denormalized “MySQLicious solution”.
This is the most flexible data structure and I guess it should scale pretty good (that is: if you do some caching).

Update May, 2006. This arcticle got quite some attention. I wasn’t really prepared for that! It seems people keep referring to it and even some new sites that allow tagging give credit to my articles. I think the real credit goes to the contributers of the different schemas:MySQLiciousscuttleToxi and to all the contributors of the comments (be sure to read them!)
P.S. Thanks to Toxi for sending me the queries for the three-table-schema, Benjamin Reitzammer for pointing me to a loughing meme article (a good reference for tag queries) and powerlinux for pointing me to scuttle.
Further reading