I am building an application for learning languages, with Rails and Postgresql.
Texts get uploaded. The texts will be of varying length, but let’s assume they’ll be 100-3000 words long.
On upload, each text position gets transformed into a “token”, representing information about the word at that position (base word, noun/verb/adjective/etc., grammar tags, definition_id).
On click of a word in the text, I need to find (and show) all other texts in the database that have words with the same attributes (base_word, part of speech, tags) as the clicked word.
The easiest and most relational way to do this is a join table TextWord, between the table Text and Word. Each text_word would represent a position in the text, and would contain the text_id, word_id, grammar_tags, start_index, and end_index.
However, if a text has between 100-3000 words, this would mean 100-3000 entries for each text object.
Is that crazy? Expensive? What problems could this lead to?
Is there a better way?
I can’t use Postgres full text search because, for example, if I click “left” in “I left Nashville”, I don’t want “take a left at the light” to show up. I want only “left” as a verb, as well as other forms of “leave” as a verb. Furthermore, I might want only “left” with a specific definition_id (ex. “Left” used as “The political party”, not “the opposite of right”).
The other option I can think of is to store a JSON on the text object, with the tokens as a big hash of hashes, or array of hashes (either way). Does Postgresql have a way to search through that kind of nested data structure?
A third option is to have the same JSON as option 2 (to store all the positions in a text), and a 2nd json on each word object / definition object / grammar object (to store all the positions across all texts where that object appears). However, this seems like it might take up more storage than a join table, and I’m not sure if it would bring any tangible benefit.
Any advice would be much appreciated.
Thanks, Michael.