Три начина за изграждане и промяна на потребителска таблица

Синопсис

Джон Доу току-що започна нова работа като технически директор в стартираща компания, подобна на Uber. Той ще трябва да мащабира, разстройва и иновира. Но засега той се нуждае от потребителска таблица.

  • Ден 1: Потребителите са прости лица с ID, имейл, парола, име и роля (клиент, шофьор или администратор).
  • Ден 2: Джон добавя телефонен номер към всички потребители.
  • Ден 3: Нови случаи на употреба, бизнесът се разраства. Потребителите се нуждаят от държава, пол и незадължителна компания.
  • Ден 4: Нека добавим биография само за потребителите на шофьори.
  • Ден 5: Някои клиенти искат да добавят персонализирани свойства към клиентския си акаунт.

Джон можеше да посрещне ежедневните нужди по безкрайно много начини. Нека разгледаме 3 сценария наред с други (Джон избира PostgreSQL).

1-ви сценарий — Пътят на Bigmouth Buffalo

Ден 1

Джон създава users таблица с 6 колони: id (uuid, първичен ключ),имейл (varchar), парола (varchar), роля (преброяване: клиент, драйвер, администратор), created_at и updated_at (клейма за време).

Ден 2

Джон променя таблицата users и добавя колона phone (varchar, nullable). Phone е nullable: потребителите, създадени в първия ден, го правят нямат телефонен номер и Джон може би никога няма да разбере техните телефонни номера.

Ден 3

Джон променя отново таблицата users и добавя country(varchar, nullable), gender(enum, nullable), икомпания (varchar, nullable).

Ден 4

Биографиите са само за шофьори. Има поне две възможности:

  1. Създайте nullable bio text колона (NULL за не-драйвери)
  2. Тъй като е само за драйвери, създайте drivers_biographies таблица с FK на users и колона bio text.

Всяка възможност има своите плюсове и минуси, Джон избира да добави нова таблица, защото не харесва твърде много NULL и е сигурен, че никога няма да добави биография към други типове акаунти. Може би той греши.

Ден 5

Клиентите вече могат да поискат потребителски полета от своите потребители. Така че това е някакъв вид динамични свойства на променливата. Джон може да добави nullable нова колона при поискване в таблицата си с потребители (клиентът иска имена на домашни любимци на потребителите, Джон добавя колона pet_name) . Но не му звучи мащабируемо, може би греши.

И така, той реши да създаде две нови таблици: properties и users_properties. Таблицата properties има 2 колони:

  • id (uuid)
  • етикет (varchar) Пример: „Име на домашен любимец“

users_properties има 3 колони:

  • user_id (чужд ключ на users таблица)
  • property_id (чужд ключ на users_properties таблица) Пример: идентификаторът на „Име на домашен любимец“
  • стойност (Ъъъ… Нещо? Зависи. И така, текст, предполагам?!)

Сега всеки път, когато клиент поиска нови свойства за своите потребители, приложението на John добавя нов запис в таблица properties. След това, за всеки нов потребител с персонализирани свойства, някои редове се добавят в таблицата users_properties за неговите собствени свойства (модел „подобен на EAV“).

Посмъртно — анализ на Мери Фубар

Изглежда има някои проблеми с този подход:

  • компания е незадължителна колона, която е празна през повечето време. Засега има само един, но какво ще кажете за ден 12?
  • drivers_biographies е маса едно към едно, прекалява.
  • Смесването на потребителски данни в две таблици е бъркотия. Защо някои полета (компания, пол) са в таблица users, а други (мобилен_телефон, име_на_любимец) в users_properties таблица (с различна схема)? Архитектурата на базата данни на Джон може да се счита за непоследователна.
  • Запитването на потребител с неговите свойства би било по-трудно от обикновено.
  • Картирането към обекти (ако приемем, че Джон използва този вид неща) също би било сложно.
  • Има 4 таблици на ден 5, само за описание на потребителите.
  • Всяка нова колона в таблица users трябва да бъде nullable: тези колони са добавени след първите вмъквания на потребители, които нямат стойност.

Може би Джон можеше да изгради по-добра архитектура.

2-ри сценарий — Пътят на Синята риба

Ден 1

Джон е предвидлив. Що се отнася до пътя на Buffalo Mouth, тойсъздава таблица users с id,имейл, парола и роля. Но той също така незабавно създава таблица users_properties (модел Entity-Attribute-Value), подобно на подхода Wordpress. users_properties е таблица с user_id (чужд ключ в таблицата на потребителите), ключ (varchar, пример: име, фирма, телефон, и т.н.) и стойност(текст).

Ден 2, 3, 4, 5

Джон е спокоен, не му се налага да променя схемите на таблиците. Програмата му просто добавя нови потребители в базата данни с техните нови свойства. Той създаде админ панел за бързо добавяне на нови свойства.

Post mortem — Анализ на Мери Фубар

Има едно предимство пред предишния дизайн. Лесно е за обяснение и разбиране, всяко свойство се намира в една уникална таблица. EAV е добре познат модел, дори ако Джон не беше чувал за него, той сам щеше да го измисли. Има само две таблици за описание на потребителските метаданни и няма да има повече. Но има и предупреждения:

  • Простите заявки стават „сложни и нечетливи“ в сравнение с класическа таблица с една колона по свойство. SELECT name FROM users WHERE age=50 AND gender='f' става:
SELECT 
  name.meta_value AS name, 
FROM 
  users_properties age, 
  users_properties name, 
  users_properties gender
WHERE age.key = ’age’ 
AND age.value = ’50'
AND age.user_id = name.user_id
AND gender.user_id = age.user_id
AND gender.key = ’gender’ and gender.value = ’f’
  • Таблицата не се чете с човешко око. SQL е за редове и колони, моделът е унищожен от EAV.

  • Дублиращите се данни са по-трудни за откриване, няма типове данни, няма йерархия на данните, няма последователност и т.н.

3-ти сценарий — Пътят на дъговата пъстърва

Ден 1

Както много други, Джон проявява интерес преди години към „NoSQL“. Той се опита и изостави MongoDB, за да се върне към първата си любов, PostgreSQL. Той си спомня усещането, че данните без схеми могат да имат ползи в някои случаи, особено в променливите метаданни. Така че този път Джон създава таблица users с id,имейл, парола, роля, метаданни (JSONB) created_at и updated_at. Колоната му с метаданни е без схема, той може да съхранява обекти като:

{"phone": "+33612345678", "company": "My company", "gender": "f"}

Ден 2, 3, 4, 5

Схемата на таблицата не се променя.

Post mortem — Анализ на Мери Фубар

Има някои предимства с този дизайн в сравнение с предишните пътища:

  • Само една таблица за описание на един обект: потребители.
  • Заявките се пишат лесно: select * from users where metadata->'age'=50
  • Данните са лесни за четене в един ред на потребител (честно казано, JSON частта е малко по-трудна за четене, ако расте).
  • EAV се избягва за дефинирани от потребителя полета.

Има и някои предупреждения:

  • Джон трябва да запомни да добави GIN индекс към метаданните.
  • Джон не трябва да добавя връзка в метаданни. С колона JSONB има голямо изкушение да добавите всичко в тази колона. Ако Джон добави нов обект, той трябва да създаде нова таблица, а не да добави подсвойство като:
{ "bookings": [
  { "id": "aaa-bb-cc", "date": "2017–01–12", "duration": "10 days"},
  { "id": "xxx-yy-zz", "date": "2017–02–10", "duration": "1 day"},
]}

Последни мисли

В моята кариера на калайджия създадох users таблица, следвайки тези три пътя (не точно, но подобни) и някои други: можем да помислим за динамично добавяне на колони и таблици, промяна за друга СУБД и т.н. През последните няколко месеца аз следва третия път. Сигурен съм, че няма „правилен“ начин и този път също има много скрити предупреждения (трябва да прочета повече), но съм добре с него за ежедневната си работа. Мигрирах наследена система с 60+ полубезполезни таблици до около 10 таблици, като премахнах таблици с метаданни. Не съм сигурен, че така или иначе е по-добре и не казвам, че по-малко таблица е по-добре, но в този конкретен случай мисля, че кодът и базата данни са по-лесни за четене, създаване, актуализиране и изтриване. Знам, че след няколко месеца ще открия нов път и ще се срамувам от това, което съм направил.

  • Пътят на Rainbow Trout е ОК за потребителската маса, не мисля, че е добра идея да добавяте JSONB навсякъде на всяка маса всеки път. Може да помогне да съхранявате само метаданни. Не отношения.
  • Не съм говорил за индекси, това не е въпросът, но моля, използвайте индекс навсякъде.
  • Говорих само за PostgreSQL, може би SQL не е нещото за изграждане на потребителска база данни.
  • Все още се учудвам, че не намерих очевидно най-добрия начин за изграждане на потребителска таблица след всички тези години. Пропуснах ли нещо?

Чувствайте се свободни да коментирате със съвети, обратна връзка и критика. Ще се радвам да науча повече.

„Hacker Noon“ е начинът, по който хакерите започват следобедите си. Ние сме част от семейството на @AMI. Сега „приемаме предложения“ и се радваме да „обсъдим възможностите за реклама и спонсорство“.

Ако тази история ви е харесала, препоръчваме да прочетете нашите „най-нови технологични истории“ и „актуални технологични истории“. До следващия път, не приемайте реалностите на света за даденост!