This is the requirement for an educational application to teach words to language learners:
- Learner can declare that he/she knows a given word
- Learner can memorize the given word (to be managed by the application later)
- Learner can choose to ignore the given word, so that it won't be given to him/her later
- Admin can ignore a word in general, so that it won't be given to any learner
- A list of words exist in database, as the reference
- Next word to be given to the student, should be new to him/her
This is our design:
Words - Id - Word - Meaning - PartOfSpeech LearnerWords - Id - LearnerId - WordId - TypeOfInteraction (ignored, memorized, memorizing, reviewing, etc.) Learners - 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?