Postgresql – How should I model a database structure to store word information on flashcards

database-designpostgresql

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

Best Answer

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.

Related Question