I have a Rails application using Postgresql.
Texts get added to the application (ranging in size -- as short as a few words, to as long as, say, 5,000 words?).
The text gets parsed, both automatically and by hand, to associate each word/position in the text with specific information (part of speech, grammar tags, lemma, definition_id).
Given a lemma, or a part of speech, or grammar tags, or a definition_id (or a combination), I need to be able to find all other text positions in the database that contain the same information (lemma / part of speech / definition_id / etc.).
There are only two options (that I can think of):
- A table to store each word position, with columns for each of the above attributes. Finding what I need would be a breeze, my life would be super easy.
- A JSON column on the Text object, to store all the word positions. Would be fewer entries, but, a) Constructing a query would be difficult, b) That search would probably be slow, and c) It might take up more storage space than a separate table.
Can I choose option 1? Is storing 1 entry for every word a very bad idea? That’s a lot of entries!
If there is another option I haven’t considered, please enlighten me.
Thanks, Michael King