0

Words, base words, and flashcards

I recently posted this question, where I asked about modeling words and base_words in a database - now that I've landed upon having separate tables, a second issue has arisen: a user adds flashcards containing words, which link to a base word, and contains a full sentence from one of the texts in our database, along with other grammatical information from our database. I'd also like to keep track of how many words the user has learnt, as well as how many base words.

Considerations

  1. Given that I'd like to keep a count (and keeping track) of the user's learnt words separately from learnt base_words, this involves having a separate table (I think). In this table, in terms of normalization/denormalization, is it ok to store all the user's learnt word_ids in one array, and then learnt base_word_ids in another array?

  2. Given that the words, base words, and sentences will already be in the database, it seems pointless to duplicate the information onto their flashcards - however, given that the student needs to study the flashcards offline, does this require duplication, or am I misunderstanding database structures? My aim is that the student can study offline, and then when he comes back online, if there has been a change to the original DB data that he has on his flashcard, that it will be updated automatically. The only new information that will go on the flashcard is the user's personal notes.

Warm regards, Michael

Improve this question
2
  • Why are you editing a question that is almost a year old with a chosen answer? If you still have a question -- ask another question.
    – Evan Carroll
    Oct 12, 2017 at 21:19
  • No question - just trying to help the community. I was appalled by how awfully I used to write questions, so just wanted to strip it down to its bare before asking a new question. Didn't change the original question, just boiled it down to be clearer.
    – michaelsking1993
    Oct 12, 2017 at 21:41

1 Answer 1

Reset to default
2

Generally speaking, databases don't store arrays.

It sounds like you need several tables to track different things.

  • A "users" table which will store the userID (primary key), user's name, and so on.
  • A "base_word" table to store the base word, a BaseWordID (primary key) and such.
  • A "Users_Words" linking table. This table would have it's own primary key, a UserID foreign key to link to the Users table, a BaseWordID foreign key link to the base_word table, and a Learnt flag to track learnt or not (possibly other columns, too?)

If they need access to the data "offline," that's not duplicating the data. That's generating a "report" from the data. This might be an HTML page derived from the data that is printed (or printed to PDF), for example. Or an Excel file dump of the data.

Improve this answer
0

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.