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:

Additionally, there are some properties that are unique to certain parts of speech. Specifically, verbs have a person, mood, and tense and adjectives and nouns have a gender and number.

Example: hacía

  • string: hacía
  • part of speech: verb
  • meaning: to do
  • lemma: hacer
  • person: 1st
  • mood: indicative
  • tense: imperfect

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: hacía, verb => 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 => hago

My question:

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?

  • 1
    I think you already have the tables and columns described you need for your database. The API services you listed will map to some simple SQL queries then. So I fail to see where there is a conceptual softwarengineering question in your question we could answer here. You next step is simply to implement the thing and try it out, and if that leads to a specific coding problem, you can ask for help (on Stackoverflow, not here, since coding questions are off-topic here).
    – Doc Brown
    Commented Sep 1, 2019 at 7:13
  • 1
    Thanks for your answer @DocBrown! I agree that I have enough info to get started. I should have been more clear in my question - I am using this as a learning experience; I'm interested in how some of the more senior engineers on this site would approach the problem. Given your answer, it seems your approach would be to just dive in naively and refactor as necessary?
    – Al Avery
    Commented Sep 1, 2019 at 15:41
  • 1
    This isn't exactly naive. But yeah, you have to start writing code at some point, and it looks like you already have enough information to do that. Commented Sep 1, 2019 at 16:38
  • 2
    There is a lot of prior work on this topic. It's not clear from your question how familiar you are with such work, but if you are not, you could do worse than start research at en.wikipedia.org/wiki/Lexical_Markup_Framework. And from a quick glance I'd say you have more work to do on your data structure design. It's not entirely clear, for example, that you have a good enough definition of word or that your outline design will capture relationships between 'words' (such as synonymy) which are probably useful in a learning support system. Commented Sep 2, 2019 at 7:22

1 Answer 1


Analysing your statements:

  1. There is a common part of properties to all the words
  2. In this common part, the part of speech acts like a type : the word may have additional properties depending on the value of part of speech -> additional properties in the same table (that may be used or not) ? or additional
  3. There is a unique id for each different word (string), regardless of its form

Now the brainstorming about potential inductions:

  • (1) requires at least one Word table with all the basic properties
  • (2) requires a place for additional properties:
    • possibility (2.a): put additional properties in the Word table, but leave them empty when they are not needed ("single inheritance" model).
    • possibility (2.b): put additional properties in a separate table, with one table for each part of speech ("class table inheritance" or "concrete table inheritance" in case several parts of speech could form a hierarchy)
    • possibility (2.c): put each property into a entity-attribute-value table
    • possibility (2.d): create a separate table for each additional property
  • Variants of (2) suggest that part of speech defines specialisations of word. So it could be analyzed further if the different part of speech are a flat structure, or form a hierarchy
  • The fact that part of speech defines specialisation with their own property raises the question if other core properties of word also could require additional properties. But at first sight, no...
  • (3) suggest that the unique id could be used to join the different tables
  • (3) means that the same word could have duplicate entries in Words, each with a different meaning. => does the different meaning affect other properties ?
  • (3) means that the same word could have duplicate entries in Words, each with a different part of speech. => does this mean that Words should be split into its lexical part (i.e. unique id and string) and its grammatical and semantic part ?

These are inductions: just ideas that pop up based on the initial findings. The next step would be to evaluate these to see if they are valid, how they could be solved, and pros and cons

Then you could design. For me, intuitively, I'd quickly restrict my choice to a class inheritance table (Lexical forms with id and string; Grammatical form with part of speech and remaining core properties; additional tables for the different part of speech) and to an Entity-field-value approach (more flexible, but more expensive). Up to you to cross check how both approaches could satisfy your different query requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.