1

Lets consider the following task: For a NLP task I am collecting news title in a database. I need to use this data for word frequency analysis, so I tokenize the title and store the words in the following way:

titles table: 

titleID    word1    word2    word3    ....   
1          0        0        1   
2          1        1        0   
3          0        0        0   
...

Every column is a different word, and i mark it 0 or 1 according to whether or not it is present in the title.

The issue is of course that each time i add a new title, there might be a word that is new and therefore I would need to add a new column for this word.

In SQL world that means a potential migration at each insert of a new row, which is not tenable. I could use nosql but I would still like that table to have relations with other tables in my database.

What would be the alternatives for my case? What is the common way to store NLP data in a database?

1 Answer 1

1

What you probably want to do is normalize your data. Please read up on it in this post: What is Database Normalization in SQL?

To summarize, you should actually restructure your table design by removing all the word columns and storing the words themselves into their own table that is referenced by your Titles table.

Your Words table would have a unique identifying column like WordId which would be referenced by your Titles table which itself would have a TitleId column.

You can then have a third table to relate the many-to-many relationship between the two tables (named something like TitleWords) and it would have TitleId and WordId as columns so you could link between the Titles and Words tables.

Implementing your database design this way not only helps your maintainability of the relational data structure, but it also reduces redundancy and saves space in your database leading to efficiencies as well.

2
  • 1
    Thanks a lot for your answer it is very useful. I am now wondering, if I apply this approach of 1 table per word, is there a know/best practice strategy to write the schema of such database ?
    – jim jarnac
    Commented Nov 24, 2020 at 18:23
  • 1
    Sorry if I wasn't clear. You should have one generic table that holds all your words. In total you'd have three tables, one called Titles, one called Words, and one that links the two together (e.g. TitleWords). Titles would have a record for each title, Words would have a record for each unique word, and TitleWords would have a record for every relationship that exists between a Title and Word.
    – J.D.
    Commented Nov 24, 2020 at 18:35

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.