Designing the database

My chatbot is going to answer queries on the Mahabharat, so my database will be a Mahabharat¹ database. Let's design one 🚧

I should be able to apply the design principles in this post to any story corpus, epic, or novel (and not just the Mahabharat). The Bible, for example, or the Harry Potter series. 🌟⚡️

Who am I designing the database for?

A chabot.

Who will use the chatbot?

Human beings.

Why will they use the chatbot?

To get quick info on the story.

What kind of info?

Info about the people in the epic, what they do, how they live day to day, how they die.

I know that my chatbot is not a story-telling bot; it is an info-supplying bot. How do human beings look for info? They do so by asking questions.

Why is the sky blue?

 

How do I dance the foxtrot?

 

When is that moron going to resign?

 

Where were they when the lights went out?

 

Who framed Peter Rabbit?

 

Applying these questions to my project, I can see two things:

• That I need to group the information into categories
• That a category should answer one - and only one - type of question
• That an answer to a question in one category can lead to further questions that can belong to either the same category or to another

I believe, in geek parlance these are known as taxonomy, ontology, and normalisation?

So, categories will be my database tables. The rows in the tables will be the Who, What, Where of that category. The columns will contain the information about one specific Who, What, Where. The info in one cell of one table can be exactly equal to the info in a cell of one or more tables. 💥

My Mahabharat database is going to have the following categories (tables):

• persons
• families
• clans
• weapons
• killings
• places

A character in the story (a person) can belong to persons, families, clans, weapons, and killings. In one table, a person can appear in one - and only one - row. In all other tables, that person can appear at several rows, but only once in each such row.

I believe, in geek parlance this is known as primary key and foreign key?

Now that I have an idea of what my database will look like, I should write it all down. So, here we go:

To sum up, for designing my database, I:

• Ask what kind of questions my database will answer.
• Group the questions into categories.
• Create a table for each category.
• Turn the sub-groups of these categories into the table columns.
• Link one table to another through a common column.

Next, I'll create these tables in MySQL on my RaspberryPi and populate them with the data.

Footnote:
1. The Mahabharat is one of the two epics of ancient India, is the longest epic in the world, and is the epic that contains the Bhagvad Gita (a mini-book that's sacred to the Hindus).