For a project I'm working on that will help people learn Spanish, I would like to create a standalone service to handle the retrieval of data about words. For this, I've captured and codified data for a few thousand words from Wiktionary. Currently, this is in JSON format, but I intend to put it into a PostgreSQL db. I am looking for advice in developing the schema of the database.
Every word has certain properties, which I have captured from Wiktionary:
part of speech
lemma(sort of like the "plain" version of the word)
Additionally, there are some properties that are unique to certain parts of speech. Specifically, verbs have a
tense and adjectives and nouns have a
part of speech: verb
meaning: to do
I intend for the API of the service to be able to the following:
- Given a particular string and part of speech, return all the possible words that match it. Example:
imperfect indicative first person of hacer and imperfect indicative third person of hacer, along with the properties of each of those two words
- Given the unique identifier for a word, return every possible form of the word. Example:
unique id of hacía=>
every possible form of hacer, along with their properties.
- Given the unique identifier for a word and a list of properties, return the form of that word that meets those properties. Example:
unique id of hacía, indicative, present, first person=>
With these intended uses and the underlying data in mind, how would you suggest I organize the db? What other considerations are relevant, if any?
More specifically, should this database consist of a single table, with all of the properties listed below in it? Or, alternatively, should it be two tables: a table with all of the words and a separate table with all of the lemmas, to which this table is linked? Or, is there a third alternative I have not thought of?