Neeg xa xov liaison database (part 1): tsim lub hauv paus moj khaum

Yuav ua li cas koj tuaj yeem txhais cov kev cai ua lag luam rau hauv cov ntaub ntawv tshwj xeeb uas siv cov piv txwv ntawm kev tsim cov neeg xa xov xwm database los ntawm kos.

• Part 1: tsim lub hauv paus ncej
• Part 2: sectioning “rau profit”

Peb lub hauv paus yuav tsis loj thiab faib, zoo li VKontakte los yog Badoo, tab sis "yog li ntawd nws yog", tab sis nws yog qhov zoo - ua haujlwm, nrawm thiab haum rau ib lub server PostgreSQL - yog li ntawd koj tuaj yeem xa ib qho piv txwv ntawm cov kev pabcuam ib qho ntawm ib sab, piv txwv li.

Yog li ntawd, peb yuav tsis cuam tshuam txog cov teeb meem ntawm kev sib faib, kev rov ua dua thiab cov txheej txheem geo-distributed, tab sis yuav tsom mus rau kev daws teeb meem hauv Circuit Court hauv cov ntaub ntawv.

Kauj Ruam 1: Qee qhov kev lag luam tshwj xeeb

Peb yuav tsis tsim peb cov lus abstractly, tab sis yuav integrate rau hauv ib puag ncig neeg social network. Qhov ntawd yog, peb cov neeg tsis "tsuas yog sib tham," tab sis sib txuas lus nrog ib leeg hauv cov ntsiab lus ntawm kev daws teeb meem kev lag luam.

Thiab cov hauj lwm ntawm kev lag luam yog dab tsi?.. Cia peb saib cov piv txwv ntawm Vasily, tus thawj coj ntawm lub tuam tsev loj hlob.

• "Nikolai, rau txoj haujlwm no peb xav tau thaj chaw hnub no!"
  Qhov no txhais tau hais tias kev sau ntawv tuaj yeem ua tiav hauv cov ntsiab lus ntawm qee qhov daim ntawv.
• "Kolya, koj puas yuav mus Dota hmo no?"
  Ntawd yog, txawm tias ib khub ntawm interlocutors tuaj yeem sib txuas lus ib txhij ntawm ntau yam.
• "Peter, Nikolay, saib hauv daim ntawv txuas rau daim ntawv teev nqi rau tus neeg rau zaub mov tshiab."
  Yog li, ib qho lus tuaj yeem muaj ob peb tus neeg tau txais kev pab. Hauv qhov no, cov lus yuav muaj Txuas cov ntaub ntawv.
• "Semyon, saib ib yam nkaus."
  Thiab yuav tsum muaj lub sijhawm los nkag rau hauv cov ntawv xov xwm uas twb muaj lawm caw ib tug tswv cuab tshiab.

Cia peb nyob ntawm daim ntawv teev npe "pom meej" xav tau tam sim no.

Yog tsis nkag siab txog cov kev siv tshwj xeeb ntawm qhov teeb meem thiab cov kev txwv uas muab rau nws, tsim siv tau database schema los daws nws yuav luag tsis yooj yim sua.

Kauj Ruam 2: Tsawg Logic Circuit Court

Txog tam sim no, txhua yam ua haujlwm zoo ib yam li email kev sib tham - ib txwm ua lag luam cuab yeej. Yog, "algorithmically" ntau yam teeb meem kev lag luam zoo ib yam, yog li cov cuab yeej los daws lawv yuav zoo sib xws.

Cia peb kho qhov twb tau txais cov duab kos duab ntawm qhov chaw sib raug zoo. Txhawm rau ua kom peb cov qauv nkag siab yooj yim, peb yuav siv cov kev xaiv thawj zaug tshaj plaws ER qauv tsis muaj teeb meem ntawm UML lossis IDEF cov ntawv sau tseg:

Hauv peb qhov piv txwv, tus neeg, cov ntaub ntawv thiab binary "lub cev" ntawm cov ntaub ntawv yog "sab nraud" cov chaw uas muaj nyob ntawm nws tus kheej yam tsis muaj kev pabcuam. Yog li ntawd, peb yuav tsuas pom lawv yav tom ntej raws li qee qhov txuas "qhov chaw" los ntawm UUID.

Kos daim duab yooj yim li sai tau - feem ntau ntawm cov neeg koj yuav qhia lawv tsis yog cov kws tshaj lij hauv kev nyeem UML / IDEF. Tab sis nco ntsoov kos.

Kauj ruam 3: Sketching lub rooj qauv

Hais txog lub rooj thiab lub npe tebCov npe "Lavxias teb sab" ntawm cov teb thiab cov ntxhuav tuaj yeem kho txawv, tab sis qhov no yog qhov teeb meem ntawm saj. Vim lub ntawm no Tensor tsis muaj cov neeg tsim tawm txawv teb chaws, thiab PostgreSQL tso cai rau peb muab cov npe txawm tias hauv hieroglyphs, yog tias lawv nyob rau hauv cov lus qhia, ces peb nyiam lub npe khoom unambiguously thiab meej kom tsis muaj discrepancies.
Txij li thaum muaj coob tus neeg sau cov lus rau peb ib zaug, ib txhia ntawm lawv kuj yuav ua li no offline, ces qhov kev xaiv yooj yim tshaj yog siv UUIDs ua tus cim tsis yog rau cov chaw sab nraud xwb, tab sis kuj rau txhua yam khoom hauv peb cov kev pabcuam. Ntxiv mus, lawv tuaj yeem tsim tawm txawm tias nyob rau ntawm tus neeg siv khoom - qhov no yuav pab peb txhawb kev xa cov lus thaum lub database tsis muaj nyob rau ib ntus, thiab qhov yuav muaj kev sib tsoo tsawg heev.

Cov qauv qauv hauv peb cov ntaub ntawv yuav zoo li no:
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
);

Qhov yooj yim tshaj plaws thaum piav txog hom ntawv yog pib "unwinding" cov duab txuas los ntawm cov rooj uas tsis hais txog lawv tus kheej tsis muaj leej twg.

Kauj Ruam 4: Tshawb nrhiav cov kev xav tau uas tsis pom tseeb

Qhov ntawd yog nws, peb tau tsim cov ntaub ntawv uas koj tuaj yeem sau tau zoo thiab xyov li cas nyeem.

Cia peb tso peb tus kheej hauv cov khau ntawm cov neeg siv ntawm peb qhov kev pabcuam - peb xav ua li cas nrog nws?

• Cov lus kawg
  no chronologically txheeb ib daim ntawv teev npe ntawm "kuv" cov lus raws li ntau yam qauv. Qhov twg kuv yog ib tug tau txais, qhov twg kuv yog tus sau, qhov twg lawv sau rau kuv thiab kuv tsis teb, qhov twg lawv tsis teb kuv, ...
• Cov neeg koom nrog kev sib tham
  Leej twg tseem koom nrog qhov kev sib tham ntev ntev no?

Peb cov qauv tso cai rau peb daws ob qho teeb meem no "feem ntau," tab sis tsis sai. Qhov teeb meem yog tias rau kev txheeb xyuas hauv thawj txoj haujlwm tsis tuaj yeem tsim index, haum rau txhua tus neeg koom (thiab koj yuav tau rho tawm tag nrho cov ntaub ntawv), thiab los daws qhov thib ob koj xav tau rho tawm tag nrho cov lus ntawm lub ncauj lus no.

Cov neeg siv tsis xav ua dej num tuaj yeem ua siab tawv hla ntawm productivity.

Kauj ruam 5: Ntse Denormalization

Ob peb qhov teeb meem yuav daws tau los ntawm cov rooj ntxiv uas peb yuav duplicate ib feem ntawm cov ntaub ntawv, tsim nyog los tsim rau lawv cov indices haum rau peb cov dej num.


Tables: RU

CREATE TABLE "РеестрСообщений"(
  "Владелец"
    uuid
, "ТипРеестра"
    smallint
, "ДатаВремя"
    timestamp
, "Сообщение"
    uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений"("Владелец", "ТипРеестра", "ДатаВремя" DESC);

CREATE TABLE "УчастникТемы"(
  "Тема"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Тема", "Персона")
);

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

Ntawm no peb tau siv ob txoj hauv kev uas siv thaum tsim cov rooj pabcuam:

• Multiplying cov ntaub ntawv
  Siv ib daim ntawv sau thawj zaug, peb tsim ntau cov ntaub ntawv taug qab hauv ntau hom kev sau npe rau cov tswv sib txawv - ob qho tib si rau tus xa thiab rau tus neeg txais. Tab sis txhua qhov kev sau npe tam sim no poob rau ntawm qhov ntsuas - tom qab tag nrho, hauv qhov xwm txheej, peb yuav xav pom tsuas yog thawj nplooj ntawv.
• Cov ntaub ntawv tshwj xeeb
  Txhua zaus koj xa cov lus hauv ib lub ncauj lus tshwj xeeb, nws txaus los xyuas seb qhov kev nkag ntawd puas muaj lawm. Yog tias tsis yog, ntxiv rau peb "dictionary".

Hauv seem tom ntej ntawm tsab xov xwm peb yuav tham txog kev siv partitioning rau hauv cov qauv ntawm peb cov database.

Tau qhov twg los: www.hab.com