I have an application where the user can click on any word in a text, which will bring back related information about the base word and the specific inflection (word form) of the word clicked. I've tried representing this in a multitude of ways, but now that I'm close to launch, I'm worried as hell about the best way to represent it (this is my first ever project with programming, so I have no idea what I'm in for).
The way I had originally set this up was that there was a
base word table that contained the
part of speech, and then an array of
word forms for each base word.
I then learned that this was a bad idea in terms of normalization, and I also realized that I needed to store specific
inflection information for the words (
person, etc.), so I rearranged it.
In the second iteration, I had three tables, a
base_word table that had the
base_word and the
part of speech, a
word table that had the
word and its
inflection information, and then a “join” table between them that contained the
base_word_id and the
However, I've been re-thinking this setup, because semantically speaking, a base word is also a word, just a specific type of word. So in this case, I feel like a “self-join” table might be most appropriate, although I could be completely overthinking this. In the “self-join” table, there would be an
is_baseword boolean attribute, which would theoretically allow all the
inflection columns to store NULL marks if it was set to true. Is this a better idea?
Note: I found these two similar questions which didn't seem to completely answer my questions, so I'm posting my twist on these here: