Моделювання даних

Шістнадцять питань до кожної таблиці — відповідь, що витримує час, і ціна хибної

Ніхто не відкриває сторінку про моделювання даних заради теорії. Її відкривають, бо зараз писатимуть схему або бо наявна вже болить: звіт, який неможливо побудувати, міграція на цілі вихідні, число, якого ніхто не може пояснити. Майже весь цей біль походить від питання, яке так і не поставили якійсь таблиці. Ось шістнадцять таких питань — із відповіддю, що витримує час, і з тим, чого коштує хибна відповідь через рік. Наступні розділи посилаються на них за номерами.

Від речень до таблиць — сутності, ключі й зв’язки

Модель зазвичай малюють тричі, на трьох рівнях деталізації, і багато суперечок про схему — це двоє людей, що говорять про різні рівні. Цей поділ сягає трирівневої архітектури комітету ANSI/SPARC 1975 року. Діаграма, яку більшість малює на першому рівні, походить від моделі «сутність — зв’язок» Пітера Чена 1976 року.

Моделювання починається з одного питання, поставленого знову й знову: про які речі говорить цей продукт і що істинне про кожну з них рівно один раз? У клієнта одна електронна адреса й багато замовлень. У замовлення одна сума й багато позицій. Скласти цей перелік правильно — більшість роботи, а надійний спосіб його знайти — записати речення, які бізнес справді вимовляє вголос. «Один рахунок може покривати кілька поставок» — це зв’язок «багато до багатьох», сказаний звичайною мовою, і він лишиться правдою після трьох переписувань застосунку. Рядок 1 — та сама перевірка для однієї таблиці: якщо ви не можете сказати одним реченням, що таке один рядок, таблиця тримає дві речі.

Діаграма вживає нотацію «пташина лапка» (crow’s foot), яку Гордон Еверест запропонував 1976 року і яку сьогодні малює більшість інструментів. Кожен кінець лінії каже, скільки примірників сутності може там стояти. Риска означає один, коло — нуль, а тризуба лапка — багато, тож коло з лапкою читається «нуль або більше», а дві риски — «рівно один». Читайте її як речення: від одного блоку вздовж лінії до позначок на дальньому кінці. Клієнт робить нуль або більше замовлень; замовлення належить рівно одному клієнтові. Позиція замовлення посередині — це рядок 5, зроблений видимим. Замовлення містить багато товарів, а товар трапляється в багатьох замовленнях, і таблиця, що їх зв’язує, виявилася окремою сутністю — з кількістю та ціною.

Ключ — це обіцянка, що рядок можна ідентифікувати, і ключі бувають двох видів. Природний ключ — значення, яким бізнес уже користується: електронна адреса, податковий номер, ISBN. Сурогатний ключ — число чи ідентифікатор, вигаданий базою й безглуздий поза системою. Природні ключі добре читаються й заощаджують з’єднання, і мають одну спільну ваду, що повторюється скрізь: бізнесові ідентифікатори змінюються. Люди змінюють електронні адреси. Компанії перереєстровуються. Книжки 1 січня 2007 року перейшли з десятизначних ISBN на тринадцятизначні, і кожна система, що мала ISBN своїм ключем, мусила змінити кожне посилання на кожну книжку. Робоча типова відповідь — рядок 2: сурогатний первинний ключ плюс унікальне обмеження на природний, тож ідентичність стабільна, а бізнесове правило однаково забезпечено.

Чотири форми виглядають гнучкими, але такими не є. Білл Карвін назвав їх у книжці «SQL Antipatterns» (2010), і, схоже, кожна довгоживуча схема відрощує принаймні одну:

Кожна нормальна форма прибирає одну конкретну аномалію

Нормалізація має репутацію академічної церемонії, і ця репутація цілком походить із того, що її викладають як драбину, на яку треба вилізти, а не як названі проблеми, яких треба уникнути. Едгар Кодд визначив першу нормальну форму 1970 року, у статті, що заснувала реляційну модель, а другу й третю — 1971-го. Кожна прибирає конкретний спосіб, у який дані псуються, коли той самий факт зберігається в кількох місцях. Вивчіть аномалію — і правило випливе саме. Вивчіть лише правило — і застосуєте його там, де воно не допомагає.

Аномалії бувають трьох видів, і одна широка таблиця замовлень показує всі три. Аномалія оновлення: факт, що зберігається в багатьох рядках, змінюють лише в частині з них — товар перейменовують у трьохстах позиціях замовлень і пропускають у триста першій. Аномалія вставлення: факт не можна записати, доки не існує інший, не пов’язаний із ним, — новий товар не додаси, доки хтось його не замовить, бо товари живуть лише в позиціях замовлень. Аномалія видалення: прибравши один факт, прибираєш інший — видаліть останнє замовлення товару, і разом із ним зникнуть його назва й ціна.

Перша нормальна форма каже, що кожна комірка містить одне значення й повторюваних груп немає. Колонка tags зі значенням «urgent, billing, vip» виглядає нешкідливо і не піддається ні фільтруванню, ні індексації, ні підрахунку, ні перейменуванню: кожна операція над нею стає маніпуляцією рядками. Те саме правило відкидає й іншу форму повторюваної групи, phone_1, phone_2, phone_3, — це список, що вдає колонки й ламається того дня, коли в когось з’явиться четвертий. Ліки в обох випадках — рядок на кожне значення.

Друга нормальна форма важить лише тоді, коли таблиця має ключ із кількох колонок, і каже, що жодне поле не має залежати лише від частини цього ключа. Таблиця позицій із ключем «замовлення + товар» не має нести назви товару. Назва залежить лише від товару, тож повторюється в кожному замовленні, де він колись був, і перейменування товару означає оновити тисячі рядків, один із яких пропустять. Третя нормальна форма поширює ту саму логіку на поля, що залежать від іншого неключового поля. Місто клієнта, збережене в замовленні, залежить від клієнта, а не від замовлення, тож коли клієнт переїде, старі замовлення тихо стверджуватимуть, що їх зроблено з нового міста.

Денормалізація тоді — свідома, зворотна оптимізація, а не невдала нормалізація, і різниця в тому, чи можете ви сказати, що вона купила і що тримає її правдивою (рядок 12). Деякі копії навіть не є надмірністю, бо фіксують інше, ніж джерело. Ціна в позиції замовлення — це ціна, яку заплатив клієнт, а не ціна товару, і вона не має змінитися, коли зміниться каталог (рядок 13). Незаконна копія виглядає точнісінько так само й не має механізму, що тримає її чесною:

Типи, NULL і відсутність — найдешевші обмеження, які взагалі бувають

Тип — найдешевше обмеження, яке взагалі буває, і добре обраний тип робить цілі категорії вад такими, яких не можна зберегти. Надійний інстинкт — брати найвужчий тип, що вміщує кожне законне значення й жодного незаконного, і не зберігати все як текст лише тому, що текст вміщує все. Текст справді вміщує все. А ще він приймає «N/A», «невідомо», « 12,50 » і порожній рядок, і кожне з цього хтось туди зрештою покладе.

Рядок про ім’я заслуговує окремого речення. 2010 року Патрік Маккензі опублікував «Falsehoods Programmers Believe About Names» — перелік із сорока припущень, які ламають справжні імена: що в кожного рівно одне ім’я й одне прізвище, що імена не змінюються, що вони вміщуються в задану кількість символів, що їх пишуть однією абеткою. Кожне з цих припущень десь є визначенням колонки. Модель, що зберігає ім’я так, як його пише людина, а окремо — як до неї звертатися, переживає всі сорок.

NULL заслуговує окремого абзацу, бо це не значення. Це відсутність значення, і вона поширюється. Порівняння з NULL ні істинне, ні хибне, а невідоме, тож рядок із NULL у колонці не збігається ні з = x, ні з <> x; сума його пропускає; а унікальне обмеження в більшості рушіїв приймає скільки завгодно NULL. Повний набір несподіванок — на сторінці про SQL. Для моделі важить рядок 9: робіть кожну колонку з NULL рішенням із підставою. Добрих підстав лише дві: значення справді необов’язкове в бізнесі або ще невідоме, коли створюють рядок. Група колонок, що бувають NULL разом, — cancelled_at, cancelled_by, cancellation_reason, — це стан, чиє місце у власній таблиці чи статусі. А колонка, що буває NULL, бо стосується лише деяких різновидів рядків, означає, що дві сутності ділять одну таблицю, — це знову рядок 1.

Час та історія — яким це було і коли ви про це дізналися

Запит, на якому спотикається більшість схем, — «а як це виглядало в березні». Таблиця, що зберігає лише поточний стан, відповідає на питання про зараз і ні на що більше, а історію потім не відновиш, бо її так і не записали. Ось чому рядки 14–16 — єдині в таблиці, яких не виправить жодна пізніша міграція. Вирішуйте рано, яким сутностям потрібна історія. Є п’ять способів її зберігати, і вони відповідають на різні питання:

Два різновиди дат постійно плутають, і рядок 15 — про те, щоб їх розрізняти: коли щось сталося у світі й коли про це дізналася ваша система. Книжка Річарда Снодграсса 1999 року про застосунки SQL, що працюють із часом, називає їх часом чинності й часом транзакції. Платіж, зроблений у п’ятницю й імпортований у понеділок, має обидва. Кожне звіряння, звіт і питання аудиту залежать від того, про який із них ідеться. Зберігання лише одного — причина, чому місячна сума змінюється після закриття місяця й ніхто не може пояснити чому.

Діаграма стежить за одним платежем протягом тижня. Клієнт заплатив 102 у п’ятницю 3 жовтня. До системи платіж потрапив у понеділок 6-го, введений як 120, а в середу 8-го хтось виправив цю друкарську помилку. Три звіти за ту п’ятницю дають три відповіді. Запущений у неділю — платежу в ньому немає, бо система ще не знала. Запущений у вівторок — показує 120. Запущений у четвер — 102. Усі три мали рацію щодо того, що знала система, коли їх запускали. Модель, що зберігає обидва часи, може на вимогу відтворити кожен із них — саме цього просить аудитор. Модель, що перезаписує, може дати лише останній і не має як пояснити, чому звіт минулого тижня казав інше.

Видалення — третє питання про час (рядок 16), і його зазвичай вирішують рефлекторно. Колонка deleted_at зберігає історію й робить випадкове видалення відновлюваним — і назавжди накладає податок на кожен запит у системі: один пропущений фільтр покаже користувачеві прибрані дані. Якщо ви це вживаєте, забезпечуйте в одному місці — поданням, репозиторієм, політикою на рівні рядків, — а не в кожному запиті. До того ж м’яке видалення — не видалення в правовому сенсі. GDPRGDPRGeneral Data Protection Regulation — закон ЄС про персональні даніЄвропейська регуляція про персональні дані: що можна збирати, на якій правовій підставі, скільки зберігати і чого людина може від вас вимагати щодо своїх даних. Вона застосовується за місцем перебування людей, а не компанії, — тому дістає до софту, збудованого будь-де.Усі терміни дає людям право на стирання персональних даних (стаття 17) і обмежує, як довго їх можна зберігати (стаття 5). Тож вирішуйте для кожної сутності: справжнє видалення — для того, що нікому не потрібне; м’яке — для помилок користувачів; анонімізація — лишити замовлення, прибрати людину — там, де бізнесу потрібен запис, але не особа; і термін зберігання, після якого зникають навіть м’яко видалені рядки. Бекапи теж рахуються. Стирання, що лишає дані в бекапі минулого місяця, потребує задокументованої відповіді на день, коли цей бекап відновлять.

Моделі для читання — схема «зірка», зернистість і схема як код

Усе наведене — для системи обліку, де найважливіше, щоб кожен запис був правильним. Модель, збудована, щоб відповідати на питання по мільйонах рядків, іде на протилежний компроміс: вільно повторює дані, щоб читання були простими й швидкими. Найпоширеніший спосіб її будувати — вимірне моделювання Ральфа Кімбалла з книжки «The Data Warehouse Toolkit» (1996). У ньому чотири кроки. Обрати бізнес-процес — продажі, відвантаження, платежі. Оголосити зернистість. Визначити виміри — те, за чим фільтрують і групують. Визначити факти — числа, які підсумовують. Другий крок важить найбільше і його пропускають найчастіше.

Зернистість — це речення, що каже, чим є один рядок таблиці фактів: один товар в одному чеку, баланс одного рахунку на кінець одного дня. Це рядок 1 із шістнадцяти, застосований до аналітики. Кожне число в таблиці фактів має бути правдою рівно на цій зернистості. Змішування зернистостей рахує двічі: денна сума, збережена поруч із позиціями, підсумовується разом із ними. Розмазати грубе число на дрібнішу зернистість — ще гірше, бо це вигадує дані. Річна сума, поділена на двадцять шість, виглядає точнісінько як двотижневий дохід, але ним не є. Кейс Robodebt нижче — це речення, вписане в урядову програму.

Схема як код, який читають інші люди

Хоч яку форму оберете, сприймайте схему як вихідний код, який читають інші люди. Імена — це інтерфейс. Колонка status у трьох таблицях, що означає три різні набори значень, коштує щодня, а колонка на ім’я flag за півтора року стає загадкою. Оберіть одну домовленість про однину чи множину в назвах таблиць, одну — для міток часу, одну — для зовнішніх ключів, і запишіть їх, щоб десята таблиця збігалася з першою. Тримайте міграції в репозиторії, переглядайте їх як будь-яку іншу зміну, і робіть кожну зворотною або явно позначеною як незворотна. Як змінити схему на живій системі, не заблокувавши її, — на сторінці про SQL; тут правило про зміст.

Ніколи не давайте старій колонці нового змісту. Колонку, зміст якої змінили на місці, читають код, звіти й споживачі нижче за потоком, які досі вірять у старий зміст, і жоден із них не падає гучно. Сторінка про управління ризиками розповідає найдорожчу версію цього: 2012 року Knight Capital перевикористала прапорець для нової функції, один сервер досі виконував код, що читав старий зміст, і фірма втратила близько 460 мільйонів доларів за 45 хвилин. Коли зміст змінюється, додайте нову колонку з новою назвою, переведіть на неї читачів і приберіть стару — і пам’ятайте, що серед читачів тепер і ті, хто споживає ваші таблиці через change data capturechange data captureпотік змін із журналу базиПотокова передача кожної вставки, зміни й видалення з власного журналу змін бази в інші системи, як це роблять інструменти на кшталт Debezium. Вона тримає другу копію синхронною без того, щоб застосунок писав двічі, ціною прив’язки кожного споживача до структури таблиць джерела.Усі терміни, і їм їхня форма важить так само, як вашому кодові.

П’ять кейсів і те, чого в кожному бракувало

Жодному з них не знадобився ні хитрий зловмисник, ні екзотичний баг. Кожен — це одне рішення моделювання, ухвалене за замовчуванням, що зустрілося зі світом.

Basecamp — кейс для рядка 3. О 7:21 за центральним часом у четвер 8 листопада 2018 року Basecamp 3 перестав приймати зміни. Його таблиця подій фіксує майже кожну дію в продукті — від публікації повідомлення до оплесків під коментарем, — а її ключем було 32-бітне ціле, що саме дійшло до 2 147 483 647. Кожна дія, що намагалася записати подію, падала, тож увесь продукт перейшов у режим лише для читання. Зміну колонки на 64-бітне ціле на такій великій таблиці оцінили в годину й сорок хвилин, а загалом продукт працював лише на читання майже п’ять годин, до 12:11. Найгостріша деталь — та, яку Девід Гайнемаєр Ганссон сам виклав у розборі. Ruby on Rails, створений у Basecamp, зробив 64-бітні ключі типовими ще у версії 5.1 2017 року, а команда працювала саме над цією таблицею в попередні дні — вона хибно оцінила, як швидко закінчується запас. Тип ключа — це межа ємності, а межам ємності потрібен алерт задовго до того, як їм знадобиться міграція.

Кейс із назвами генів показує тип, обраний інструментом, а не людиною. 2016 року Марк Цімман, Йотам Ерен і Ассам Ель-Оста просканували додаткові електронні таблиці опублікованих статей з геноміки й знайшли назви генів, мовчки перетворені в 987 файлах із 704 статей, — приблизно в п’ятій частині статей, що подавали списки генів в Excel. SEPT2 перетворився на 2 вересня, MARCH1 — на 1 березня, а деякі ідентифікатори стали числами з рухомою комою. Електронна таблиця вгадувала тип за тим, на що схоже значення, і вгадувала хибно. Повторне дослідження 2021 року виявило, що частка зросла до 30,9%. Ліки, що зрештою спрацювали, прийшли з іншого боку. 2020 року Комітет із номенклатури генів HUGO перейменував 27 генів — SEPT1 став SEPTIN1, MARCH1 — MARCHF1, — почасти для того, щоб електронні таблиці дали їм спокій. Ціла наукова галузь змінила свої природні ідентифікатори, бо інструменти раз у раз хибно визначали їхній тип. Це рядки 2 і 6–10 водночас: ідентифікатор, схожий на число чи дату, — це текст, а природні ідентифікатори таки змінюються.

Де це вироджується

Моделі даних рідко підводять у день, коли їх проєктують. Вони підводять першого разу, коли світ робить щось, чого модель не передбачила. Майже кожен такий збій — одне з цього:

• Схема, зліплена під теперішній екран, яку доведеться мігрувати першого ж разу, коли екран зміниться.
• Таблиця, що тримає дві сутності, розрізнені колонкою типу й набором колонок, які бувають NULL (рядок 1).
• Природний ключ як первинний — доки бізнес не змінить значення, з якого той зроблено (рядок 2).
• 32-бітний ключ у таблиці, що рахує події, без жодного алерту на залишок запасу (рядок 3).
• Гроші в колонці з рухомою комою і мітки часу без поясу (рядки 6–7).
• Колонки з NULL за замовчуванням і замінники, що означають «невідомо» для одних читачів і нічого — для інших (рядок 9).
• Денормалізована копія, чесності якої ніщо не тримає: вона тихо розходиться, і їй усе одно вірять (рядок 12).
• Збережено лише поточний стан, тож перше ж питання про минулий квартал не має відповіді (рядок 14).
• Таблиця фактів без оголошеної зернистості, тож суми рахують двічі або розмазують одне число на рядки, яких воно ніколи не описувало.
• Стара колонка з новим змістом, яку читають код і звіти, що досі вірять у старий.

Чесна межа в тому, що модель може забезпечити лише те, що хтось зрозумів. Нормальні форми прибирають надмірність, обмеження відхиляють неможливі рядки, а типи — неможливі значення, але жодне з них не скаже, що правдоподібне число вигадано, що «рік» обчислили вручну чи що значення, яке дві системи обидві називають NULL, означає дві різні речі. Це рішення про зміст, ухвалені людьми, які знають бізнес, а схема — лише місце, де їх записано. Модель, якої ніхто не може пояснити реченнями, — ще не модель, хоч би як її нормалізували.

Практична перевірка займає пів дня. Для десяти найбільших таблиць напишіть по одному реченню про те, що таке рядок; кожна таблиця, якій потрібні два речення, — знахідка. Перелічіть усі 32-бітні ключі та їхній поточний максимум проти 2 147 483 647. Пошукайте в схемі колонки з рухомою комою, що тримають гроші, і мітки часу без поясу. Порахуйте колонки з NULL і для кожної спитайте, яка з двох добрих підстав до неї застосовна. І спитайте фінансову команду, на яке питання про минуле вона не змогла відповісти в минулому кварталі.

Коли це застосовувати

Пов’язані сторінки

• РозробкаSQLSQL — це мова, якою ви просите в реляційної бази дані, і спосіб покластися на гарантії, яких ваш застосунок сам не забезпечить. Ви описуєте результат, планувальник обирає шлях, а транзакції вирішують, що бачать паралельні запити. Сторінка починається з шістнадцяти несподіванок — хибних рядків, повільних запитів, загублених оновлень — із правилом за кожною і способом виправити; далі — як обчислюється запит, планувальник та індекси, транзакції й ізоляція, обмеження й зміни схеми, рушій у продакшні, п’ять задокументованих кейсів і де все це вироджується.
• РозробкаNoSQLNoSQL — парасолькова назва баз даних, які не зберігають реляційних таблиць із запитами SQL: сховищ «ключ — значення», документних, ширококолонкових і графових. Більшість із них кладе кожен запис на вузол за ключем, який обираєте ви, і цей ключ вирішує, які запити дешеві, які дорогі, а які неможливі. Сторінка починається з шістнадцяти речей, які реляційна база робила за вас, і того, що робити натомість; далі — чотири сімейства, вибір ключа партиціювання, репліки й конфлікти, моделювання під патерни доступу, експлуатація, п’ять задокументованих кейсів і де все це вироджується.
• РозробкаДизайн APIДизайн API — це робота над тим, що інші програми можуть надсилати вашій, що вони отримають у відповідь і які з цих обіцянок ви триматимете роками. Усе, що споживач може спостерегти, стає частиною обіцянки, і забрати її самотужки ви не можете. Сторінка починається з шістнадцяти рішень, які ухвалює кожен HTTP-API, — що обрати за замовчуванням і чого коштує змінити кожне пізніше; далі — що входить у контракт, методи, коди статусу й помилки, поведінка під повторами й навантаженням, хто що може робити, зміна опублікованого API, п’ять задокументованих кейсів і де все це вироджується.