Три начина за изграждане и промяна на потребителска таблица
Синопсис
Джон Доу току-що започна нова работа като технически директор в стартираща компания, подобна на 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
Биографиите са само за шофьори. Има поне две възможности:
- Създайте nullable bio text колона (NULL за не-драйвери)
- Тъй като е само за драйвери, създайте
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. Сега „приемаме предложения“ и се радваме да „обсъдим възможностите за реклама и спонсорство“.
Ако тази история ви е харесала, препоръчваме да прочетете нашите „най-нови технологични истории“ и „актуални технологични истории“. До следващия път, не приемайте реалностите на света за даденост!