Conversations in relational databases

2021-2-2 by Chris

There are many different approaches for mapping conversations in databases. In this chapter I would like to explain you which approaches are suitable and why.

To decide which database is the right one to represent a planned chatbot conversation, you first need to know which structures can be identified within conversations. I have already written a separate article about this topic in which I have shown why conversations consists of network like structures.

Relational approaches

Have you ever wondered why relational data is usually managed in tabular form? If you look at a classic CRUD, the reason for that will quickly clear up.

There is often a tabular or list-like overview from which actions such as CREATE, EDIT and DELETE can be executed. The visualization of the data corresponds to their representation in the database. This has the great advantage that you do not have to translate what you see into a different structure for the database. A tabular CRUD is a kind of WYSIWYG (What you see is what you get) for tabular data. Rows and columns in a list of records represent rows and columns in a database table.

It would require a lot of conecptual effort and coding for developers to display the data in the frontend, for example, as a tree or network. In this NestedSet example on GitHub you'll see what basic programming is required to store trees in a MySQL table in order to read them out in just one query. The generation of the tree in the frontend also requires much more logic than a simple table.

What does this mean for the storage of network structures?

Networks are far more complex than trees. Because they make it possible that different branches can grow together again and thereby form a new trunk. So we have a finite number of items, each with n links to other items, which in turn have n links to other items.

Transferring to a model of chatbot like questions and answers we come to the following conclusion:

  • We need a table where we can store the questions with their properties
  • We need a table where we can store the suggestions along with their properties
  • We need a table in which we can store the links between the questions and the possible answers

Questions:

idlabel
1Hey! How are you?
2Why are you stressed?
3What about your job?
4What do you want to do next?

Suggestions:

idlabel
1I'am stressed
2I'am fine
3My job is stupid :-(
4It’s not your business.
5My boss is stupid
6Please! Stop talking! You stupid bot!
7Book a holiday for me.

Edges:

idfromtofrom_typeto_type
111questionsuggestion
212questionsuggestion
323questionsuggestion
424questionsuggestion
534questionsuggestion
635questionsuggestion
714questionquestion
862questionquestion
946questionsuggestion
1047questionsuggestion
1112suggestionquestion
1124suggestionquestion
1134suggestionquestion
1154suggestionquestion

As you can see it is difficult to map network-like conversations in tabular form, although it is theoretically possible. The problem we face here is essentially that the data is unstructured. Questions can be connected with answer options but also directly with other questions. Answers lead to further questions. If you increase the number of items and add more item types, the problem becomes much more complex.

Also, splitting the edge table into multiple tables with unique connection types would not reduce the complexity here and would only move the problem.

Imagine what would happen, if you had to build an editor for conversations based on this data model. Although it would be theoretically possible, it is neither elegant nor simple.

In addition to storage, another problem occurs at this stage: Imagine you have to read the next meaningful question from the database. And the best elegant, with only one query and without much detours. You will not succeed with a language like SQL. You'll need to use recurring subqueries or stored procedures and you would have to design a relatively complex program logic. A possible procedure might look like this:

  1. Select the first question from the database
  2. Select all the answer options that belong to this question
  3. Check if the question or one or more of these answers have been fulfilled
  4. Select the next questions for each answer
  5. Start the procedure for these questions again from the beginning ...

Document stores

Pure document stores like MongoDB have similar problems. You can either choose to nest your data at the document level or, alternatively, reference it at the level of the records. In the end, however, you will not find a way to elegantly store and read unstructured connections. The problem remains the same.

Graph approaches

Surely you've already guessed it. The only reasonable way to represent unstructured connections between different items are graph databases. They allow you to map your network structure exactly as you would expect it to be in reality. This gives you the possibility of storing the conversation structure the same way you would design it with an editor.

Here at Wanderer.ai, that's exactly what we're trying to do. We use a graph approach to manage all of the data in a possible conversation. At the same time, we can use the graph to traverse the data in an elegant way.

Title image from Pixabay