1

The question is about my database design. Is it OK?

I feel uncomfortable about having 2 separate WordTraining and SyllableTraining tables and about too simple design (not much normalized). Maybe there are any other issues.

My web-app teaches kids to read syllables and words.

First the user (kid) chooses - whether to train to read syllables, or to train to read (whole) words.

Then the user is presented with words/syllables from a fixed list (WordBank and SyllableBank Tables below), if the user fails a read exercise - the app asks the same question again next time and then periodically asks again at increasing time intervals. If the user sees the word/syllable for the first time and passes the read exercise at the first attempt - this word/syllable would never be shown to the user again (for the exception of the special Cram Mode when the user can practise all words from the database regardless of his previous test results).

I expect not more than 60 000 words / 300 syllables / ~10 mln users.

My database design is as follows:

WordBank +----+-------+------------------+ | id | Word | SyllabilizedWord | +----+-------+------------------+ | 1 | hello | hel/lo | | 2 | papa | pa/pa | +----+-------+------------------+ SyllableBank +----+----------+ | id | Syllable | +----+----------+ | 1 | hel | | 2 | lo | +----+----------+ WordTraining +----+--------+------------+---------------+--------+--------+--------+ | id | Failed | NextRep | FirstSeenDate | Ignore | WordId | UserId | +----+--------+------------+---------------+--------+--------+--------+ | 1 | True | NULL | 2020-02-26 | False | 1 | 1 | | 2 | True | 2020-02-30 | 2020-02-26 | False | 4 | 2 | | 3 | False | NULL | 2020-02-26 | False | 7 | 3 | +----+--------+------------+---------------+--------+--------+--------+ SyllableTraining +----+--------+------------+---------------+--------+------------+--------+ | id | Failed | NextRep | FirstSeenDate | Ignore | SyllableId | UserId | +----+--------+------------+---------------+--------+------------+--------+ | 1 | True | NULL | 2020-02-26 | False | 1 | 1 | | 2 | True | 2020-02-30 | 2020-02-26 | False | 4 | 2 | | 3 | False | NULL | 2020-02-26 | False | 7 | 3 | +----+--------+------------+---------------+--------+------------+--------+ User +----+---------------+------------+--------------+---------+---------------+ | id | LoginName | FullName | Email | PswHash | LastLoginDate | +----+---------------+------------+--------------+---------+---------------+ | 1 | johnLoginName | John Black | [email protected] | acb3456 | 2020-02-22 | +----+---------------+------------+--------------+---------+---------------+

WordTraining Table - Once the user (who clicked button (chose) Word-trainig Mode) is presented a word for the first time (sees it first) - a record is created. WordId is a ForeignKey matching with (primary key) of the table Word (all words available for practise), UserId is a foreign key for respective User. Ignore atribute means that user wants to see respective word (exercise with it) no more.

WordBank.SyllabilizedWord - is just to show a prompt how to syllabilize the word correctly.

More detailed app logic:

  1. I have multiple users
  2. For each user there are a few training modes possible (train read whole words, train read syllables, 3rd mode is complicated to describe)
  3. For each user I store statistics - ids of word or syllable the user was shown (at least once) + whether the user failed or passed the exercise + date when user was shown that kind of Problem.

Algorithm

  1. User logs in
  2. User selects mode (train syllables or words). In the future different modes might be added or initial modes can be often modified.
  3. At first start of the app when user clicks "Next Problem" button, a new (unseen yet) Problem is presented to the user. He answers and the Problem (respective word or syllable) is marked passed or failed. Plus FirstSeenDate table-field (WordTraining or SyllableTraining table - depending on what user chose to practice) is set (ISO-date when the user saw this Problem).
  4. At the second start of the app when user clicks "Next Problem" button, problem selection is like this: If there are any Failed Problems - show them all first. If there are no Failed Problems - show unseen-yet Problems. If Failed (at previous game) Problem is passed during this time, Problem is marked "Passed" (Fail is false), and the field NextRep (next repetition (review)) is set (ISO-date when to show that problem to the user again). Field NextRep is set to 2 * (todayDate - FirstSeenDate) - but this algorithm is subject to change and might be very complicated.
  5. At third start of the app when user clicks "Next Problem" button - If there are any NextRep due today (or before today = missed) - show them all first. Then logic is like in item 4 above (show all failed, then unseen). If NextRep Problem is passed, Field NextRep is set to new value = 2 * (todayDate - (current)NextRep) - - but this algorithm is subject to change and might be very complicated.
Improve this question

2 Answers 2

Reset to default
1

It looks like the combination of WordId and UserId is unique. You could use this as a composite primary key and remove the Id column. Alternately (if you need the Id for other reasons) you could enforce unique combinations with an index. Either way helps to keep your data clean, and saves you from adding queries to check that you're not creating duplicates.

Also, the tables for words and syllables are almost identical. You could combine them into Bank and Training, with a new field to indicate if it's a word or syllable. This could halve the number of queries you need to write, and the associated bugs. It would also make it easy to extend your system to handle phrases, sentences, etc. OTOH it could be a problem if in the future you need extra fields for words but not syllables.

Improve this answer
1

For the purpose of what you are trying to achieve, your database works fine.

There is no redundant data showing (which is one of the requirements of normalization).

WordTraining and SyllableTraining reference to the words/syllables being tested and to the users, so that part is correct.

Overall, its a good design.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.