Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Pond's Seventh Law Inspires a Question: Elegance Serving Randomness

Pond's Seventh Law Inspires a Question: Elegance Serving Randomness

  • Comments 1
  • Likes

Marc left a great question on a Pond's Laws post from July of 2006:

Hi,

I have a flashcard system that randomly pulls a word from the database.  I also have a testing module that allows a user to test him or her self on words.  For each word the user answers incorrectly it is flagged.

I want to have an option in the flashcard system that is a checkbox that says "Test on words answered incorrectly more frequently"  when the checkbox is checked the words answered incorrectly will appear more frequently.  Any idea how to simulate the "frequently".  Remember that the flashcard grabs a word randomly too.  Thanks, Marc

Great question, Marc.  I think I can name this tune in two tables and a randomization function.  Here's where a little elegance will serve us well from a functional perspective -- no Laws are being violated!

First let's build a table for your flashcards..

create table FlashCard (
      FlashCardId int identity(1,1),
      FlashCardText nvarchar(200)
      )

Hopefully this makes sense..  each flashcard has an associated integer key.

Then let's build a table that we'll use to decide which card to flash; we'll call it the Candidate table.  It'll have an id of its own as well as a foreign key to the FlashCard table..

create table Candidate (
      CandidateId int identity(1,1),
      FlashCardId int
      )

Every time you add a record to the FlashCard table, you also add a record to the Candidate table.  Every time the user misses a word, add another record (or two, or three..  however many you need to make the missed words appear with the desired frequency) to the Candidate table, and run your SELECT RAND (SQL2K/SQL2K5/SQL2K8) until it matches a CandidateId value in Candidate, and use the foreign key back to FlashCard to retrieve the word.  With more Candidate records for more frequently missed words, those words will be more likely to be selected.

This approach strikes me as a very elegant, yet simple, highly functional, mostly set-based solution to Marc's challenge.  Properly indexed and coded, it should perform well with tens of millions of flashcards.  Given the number of randomly generated numbers this approach will throw away, your code may actually run faster with a greater number of Candidates; there's likely a slick set-based way to restrict the output of the RAND function that I haven't thought of yet.  If it comes to me I'll post an update.

I hope this adequately answers your question, Marc; please let me know if there's anything more you need..  or if you've got a better mousetrap (this last, of course, goes for anyone..!

Thanks for your comment!

     -wp

Comments
  • Welcome to installation #118 of Log Buffer , the weekly review of database blogs. Today, we’re gonna

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment