This is the requirement for an educational application to teach words to language learners:

  1. Learner can declare that he/she knows a given word
  2. Learner can memorize the given word (to be managed by the application later)
  3. Learner can choose to ignore the given word, so that it won't be given to him/her later
  4. Admin can ignore a word in general, so that it won't be given to any learner
  5. A list of words exist in database, as the reference
  6. Next word to be given to the student, should be new to him/her

This is our design:

    - Id
    - Word
    - Meaning
    - PartOfSpeech

    - Id
    - LearnerId
    - WordId
    - TypeOfInteraction (ignored, memorized, memorizing, reviewing, etc.)

    - Id
    - Name

And this is a pseudo-query that we execute to find the next new word for a given learner:

select top 1 * 
from Words 
where Id not in 
    select WordId 
    from LearnerWords
    where LearnerId = @learnerId

Of course the overall design and query is much more complex and more details are in action in selecting next new word for a given learner.

Now, imagine that words list contains 100K words, and a learner has already learnt more than 5K words. Using the given query, this gets slower and slower and slower by more learners learning more words.

Is there a better design for these types of business requirements? How to design for scale in this case?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.