🥇Baza danych komunikatora (część 1): projektujemy szkielet bazy

Jak można przetłumaczyć wymagania biznesowe na konkretne struktury danych na przykładzie projektowania bazy dla komunikatora od podstaw.

Część 1: projektowanie struktury bazy
Część 2: sekcjonowanie "na żywo"

Baza danych komunikatora (cz. 1): projektowanie szkieletu bazy
Nasza baza nie będzie tak masywna i rozproszona, jak u ВКонтакте, lub Badoo, a „żeby było”, ale żeby było dobrze — funkcjonalnie, szybko i mieściło się na jednym serwerze. PostgreSQL — żeby można było uruchomić oddzielny egzemplarz usługi gdzieś po stronie, na przykład.

Dlatego nie będziemy poruszać kwestii shardingu, replikacji i systemów geograficznie rozproszonych, ale skoncentrujemy się na rozwiązaniach schematycznych w obrębie DB.

Krok 1: Trochę specyfiki biznesowej

Nasz system wymiany wiadomości będziemy projektować nie abstrakcyjnie, ale wkomponując go w otoczenie korporacyjnej sieci społecznościowej.Oznacza to, że ludzie nie „po prostu wymieniają wiadomości”, ale komunikują się w kontekście rozwiązania określonych problemów biznesowych.

Jakie problemy mogą mieć firmy? Przyjrzyjmy się na przykładzie Wasilija — kierownika działu rozwoju.

„Nikołaj, ten patch musi być gotowy już dziś!”
Oznacza to, że rozmowa może toczyć się w kontekście jakiegoś dokumentu.
„Kolia, grajmy wieczorem w Dota?”
Oznacza to, że nawet w przypadku jednej pary rozmówców, komunikacja może się jednocześnie toczyć na różne tematy..
„Piotr, Nikołaj, zerknijcie do załącznika, jest cennik na nowy serwer.”
Zatem jedno wiadomość może mieć kilku adresatów.Przy tym wiadomość może zawierać załączniki,.
„Semen, zerknij także ty.”
I powinna być możliwość zaproszenia nowego uczestnika do już istniejącej rozmowy. Na razie zatrzymamy się na tym wykazie „oczywistych” potrzeb..

Bez zrozumienia aplikacyjnej specyfiki zadania oraz narzuconych mu ograniczeń, zaprojektowanie

efektywnego schematu DB dla jego rozwiązania jest praktycznie niemożliwe. Krok 2: Minimalny logiczny schemat

Schemat wygląda na bardzo przypominający korespondencję e-mailową — tradycyjne narzędzie prowadzenia biznesu. Tak, „algorytmicznie” wiele zadań biznesowych jest do siebie podobnych, dlatego narzędzia do ich rozwiązywania będą strukturalnie podobne.

Utrwalmy już otrzymany logiczny schemat relacji encji. Dla uproszczenia zrozumienia naszej modelu skorzystamy z najprostszej wersji graficznej

modelu ER bez komplikacji UML lub notacji IDEF: без усложнений UML или IDEF-нотаций:

Baza danych komunikatora (cz. 1): projektowanie szkieletu bazy

W naszym przykładzie postać, dokument i binarne „ciało” pliku to „zewnętrzne” byty, które istnieją niezależnie od naszej usługi. Dlatego w dalszej części będziemy je postrzegać jako pewne odnośniki „gdzieś” według UUID.

Rysujcie schematy w jak najprostszy sposób — większość osób, którym je pokażecie, nie jest ekspertem w czytaniu UML/IDEF. Ale — rysujcie koniecznie.

Krok 3: Szkicujemy strukturę tabel

O nazwach tabel i pólDo "rosyjskich" nazw pól i tabel można podchodzić różnie, ale to kwestia gustu. Ponieważ w naszym "Tenzorze" nie mamy zagranicznych deweloperów, a PostgreSQL pozwala nam nadawać nazwy nawet w hieroglifach, jeżeli są zamknięte w cudzysłowach, więc wolimy nazywać obiekty jednoznacznie, aby uniknąć nieporozumień.
Ponieważ wiadomości pisze nam wiele osób jednocześnie, część z nich może to robić w trybie offline, to najprostszym rozwiązaniem jest używanie UUID jako identyfikatorów nie tylko dla zewnętrznych bytów, ale także dla wszystkich obiektów w naszej usłudze. Co więcej, można je generować nawet po stronie klienta — to pomoże nam wspierać wysyłanie wiadomości podczas krótkotrwałej niedostępności Bazy Danych, a prawdopodobieństwo kolizji jest ekstremalnie małe.

Robocza struktura tabel w naszej bazie przyjmie taki kształt:
Tabele: RU

CREATE TABLE "Temat"(
  "Temat"
    uuid
      PRIMARY KEY
, "Dokument"
    uuid
, "Nazwa"
    text
);

CREATE TABLE "Wiadomość"(
  "Wiadomość"
    uuid
      PRIMARY KEY
, "Temat"
    uuid
, "Autor"
    uuid
, "DataCzas"
    timestamp
, "Tekst"
    text
);

CREATE TABLE "Adresat"(
  "Wiadomość"
    uuid
, "Osoba"
    uuid
, PRIMARY KEY("Wiadomość", "Osoba")
);

CREATE TABLE "Plik"(
  "Plik"
    uuid
      PRIMARY KEY
, "Wiadomość"
    uuid
, "BLOB"
    uuid
, "Nazwa"
    text
);

Tabele: 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
);

Najprostszym sposobem na opisanie formatu jest zaczynanie od „rozkręcania” grafu powiązań od tabel, które nie mają odnośników nigdzie.

Krok 4: Ustalamy nieoczywiste potrzeby

Wszystko, zaprojektowaliśmy bazę, w której można doskonale pisać i jakoś czytać.

Wyobraźmy sobie, że jesteśmy użytkownikiem naszej usługi — co chcielibyśmy robić za jej pomocą?

Ostatnie wiadomości
To chronologicznie uporządkowany rejestr „moich” wiadomości według różnych kryteriów. Gdzie jestem jednym z adresatów, gdzie jestem autorem, gdzie napisano do mnie, a nie odpowiedziałem, gdzie nie otrzymałem odpowiedzi, …
Uczestnicy korespondencji
Kto w ogóle bierze udział w tej długiej-długiej rozmowie?

Nasza struktura pozwala na rozwiązanie obu tych zadań „w ogóle”, ale nie szybko. Problem polega na tym, że do sortowania w ramach pierwszego zadania nie można utworzyć indeksu, odpowiedniego dla każdego z uczestników (i będzie trzeba wydobyć wszystkie zapisy), a do rozwiązania drugiego potrzeba wydobyć wszystkie wiadomości na dany temat.

Nieprzewidziane użytkowe zadania mogą nałożyć poważny krzyżyk na wydajność.

Krok 5: Rozsądna denormalizacja

Oba nasze problemy mogą zostać rozwiązane przez dodatkowe tabele, w które będziemy duplikować część danych, potrzebnych do utworzenia na nich odpowiednich do naszych zadań indeksów.
Baza danych komunikatora (cz. 1): projektowanie szkieletu bazy

Tabele: RU

CREATE TABLE "RejestrWiadomości"(
  "Właściciel"
    uuid
, "TypRejestru"
    smallint
, "DataCzas"
    timestamp
, "Wiadomość"
    uuid
, PRIMARY KEY("Właściciel", "TypRejestru", "Wiadomość")
);
CREATE INDEX ON "RejestrWiadomości"("Właściciel", "TypRejestru", "DataCzas" DESC);

CREATE TABLE "UczestnikTematu"(
  "Temat"
    uuid
, "Osoba"
    uuid
, PRIMARY KEY("Temat", "Osoba")
);

Tabele: 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)
);

Tutaj zastosowaliśmy dwa typowe podejścia, stosowane podczas tworzenia tabel pomocniczych:

Mnożenie zapisów
Tworzymy kilka zapisów po jednej podstawowej wiadomości w różne rodzaje rejestrów dla różnych właścicieli — zarówno dla nadawcy, jak i dla odbiorcy. Dzięki temu każdy z rejestrów teraz układa się na indeks — bo w typowym przypadku chcielibyśmy widzieć tylko pierwszą stronę.
Unikalizacja zapisów
Przy każdej wysyłce wiadomości w konkretnej tematyce wystarczy sprawdzić, czy taki zapis już istnieje. Jeśli nie — dodajemy go do naszego „słownika”.

W następnej części artykułu mowa będzie o wdrożeniu partycjonowania w strukturę naszej bazy.

Źródło: habr.com