Database of the Messenger (Part 1): Designing the Framework

How to translate business requirements into specific data structures using the example of designing a database for a messenger from scratch.

• Part 1: designing the database framework
• Part 2: partitioning 'live'

Our database will not be as large and distributed as VKontakte or Badoo, but rather "just enough" that performs well—functionally, quickly, and fits on a single server. PostgreSQL—to allow deploying a separate instance of the service somewhere on the side, for example.

Therefore, we will not touch on issues of sharding, replication, and geo-distributed systems, but focus on schema solutions within the database.

Step 1: A bit of business specificity.

We will design our messaging exchange not abstractly but integrate it into the environment of a corporate social network.This means that people are not just "chatting" but communicating with each other in the context of solving specific business tasks.

What kind of tasks does a business have? Let's look at the example of Vasily—the head of the development department.

• "Nikolai, we need a patch on this task by today!"
  This means that exchanges can happen in the context of some Housing: rent and mortgage.
• "Kolya, wanna play Dota tonight?"
  So even between one pair of interlocutors, communication can occur simultaneously on different topics..
• "Petr, Nikolai, check the attached price list for the new server."
  Thus, one message can have multiple recipients.At the same time, a message can contain attached files..
• "Semyon, you take a look too."
  And there should be an option to invite a new participant into an ongoing conversation. We'll stop here for now on this list of "obvious" needs..

Without understanding the application-specific task and its constraints, designing an

effective database schema for its solution is practically impossible. Step 2: Minimal logical schema.

So far, the schema looks very much like an email exchange—a traditional business communication tool. Indeed, "algorithmically," many business tasks are alike, and thus the tools for their resolution will be structurally similar.

Let's fix the already obtained logical schema of entity relationships. For simplicity in understanding our model, we will use the most primitive representation of an

ER model without the complexities of UML or IDEF notations: without complicating UML or IDEF notations:

In our example, the persona, document, and binary "body" of the file are considered "external" entities that exist independently of our service. Therefore, we will refer to them as links "somewhere" by UUID from now on.

Draw the diagrams as simply as possible — most of those to whom you will show them are not experts in reading UML/IDEF. But do draw them anyway.

Step 3: Drafting the table structure

About table and field namesYou can have different opinions about 'Russian' names of fields and tables, but it's a matter of taste. Since we don't have foreign developers in 'Tensor', and PostgreSQL allows us to use names even in hieroglyphs, as long as they are enclosed in quotes , we prefer naming objects clearly and understandably to avoid misunderstandings.Let's look at the resulting plan:
Since messages are written by multiple people at once, some of them may even do so in offline mode, the simplest option is to use UUIDs as identifiers not only for external entities but also for all objects within our service. Moreover, they can even be generated on the client side — this will help us support message sending during temporary database unavailability, and the likelihood of collisions is extremely low.

The draft structure of the tables in our database will look like this:
Tables: RU

CREATE TABLE "Тема"(
  "Тема"
    uuid
      PRIMARY KEY
, "Документ"
    uuid
, "Название"
    text
);

CREATE TABLE "Сообщение"(
  "Сообщение"
    uuid
      PRIMARY KEY
, "Тема"
    uuid
, "Автор"
    uuid
, "ДатаВремя"
    timestamp
, "Текст"
    text
);

CREATE TABLE "Адресат"(
  "Сообщение"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Сообщение", "Персона")
);

CREATE TABLE "Файл"(
  "Файл"
    uuid
      PRIMARY KEY
, "Сообщение"
    uuid
, "BLOB"
    uuid
, "Имя"
    text
);

Tables: EN

CREATE TABLE theme(
  theme
    uuid
      PRIMARY KEY
, document
    uuid
, title
    text
);

CREATE TABLE message(
  message
    uuid
      PRIMARY KEY
, theme
    uuid
, author
    uuid
, dt
    timestamp
, body
    text
);

CREATE TABLE message_addressee(
  message
    uuid
, person
    uuid
, PRIMARY KEY(message, person)
);

CREATE TABLE message_file(
  file
    uuid
      PRIMARY KEY
, message
    uuid
, content
    uuid
, filename
    text
);

The simplest way to describe the format is to start "unpacking" the relationship graph from tables that do not reference anyone.

Step 4: Identifying non-obvious needs

That's it, we have designed a database that we can write to excellently and somehow read.

Let's put ourselves in the shoes of a user of our service — what would we want to do with it?

• Recent messages
  This a chronologically sorted registry of "my" messages based on various criteria. Where I am one of the recipients, where I am the author, where I was written to but didn’t respond, where I wasn’t responded to, …
• Participants in the correspondence
  Who is actually involved in this long, long chat?

Our structure allows us to solve both of these tasks in general, but not quickly. The problem is that for sorting within the framework of the first task, it is impossible to create an index, suitable for each of the participants (and we would have to extract all records), and to solve the second one, we need to extract all messages on the topic.

Unforeseen user tasks can put a serious strain on performance.

Step 5: Reasonable Denormalization

Both of our problems can be solved with additional tables, where we will duplicate part of the data, necessary for forming suitable indexes for our tasks.


Tables: RU

CREATE TABLE "MessageRegistry"(
  "Owner"
    uuid
, "RegistryType"
    smallint
, "DateTime"
    timestamp
, "Message"
    uuid
, PRIMARY KEY("Owner", "RegistryType", "Message")
);
CREATE INDEX ON "MessageRegistry"("Owner", "RegistryType", "DateTime" DESC);

CREATE TABLE "ThemeParticipant"(
  "Theme"
    uuid
, "Person"
    uuid
, PRIMARY KEY("Theme", "Person")
);

Tables: EN

CREATE TABLE message_registry(
  owner
    uuid
, registry
    smallint
, dt
    timestamp
, message
    uuid
, PRIMARY KEY(owner, registry, message)
);
CREATE INDEX ON message_registry(owner, registry, dt DESC);

CREATE TABLE theme_participant(
  theme
    uuid
, person
    uuid
, PRIMARY KEY(theme, person)
);

Here we applied two typical approaches used when creating auxiliary tables:

• Record multiplication
  We create several consequential records from one original message record in different types of registries for different owners — for both the sender and the recipient. Now each of the registries fits the index — since in a typical case, we would want to see only the first page.
• Record uniqueness
  When sending a message within a specific topic, it is sufficient to check whether such a record already exists. If not, we add it to our 'dictionary'.

In the next part of the article, we will discuss the implementation of partitioning into our database structure.

Source: habr.com