Archive for June, 2012

Entity–attribute–value data model in MySQL

Tuesday, June 19th, 2012

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

Entity–attribute–value (EAV) е един интересен модел за представяне на данни, който се различава от традиционалния релационен модел. При него данните се представят чрез тройки “запис”-“свойство”-“стойност”. EAV модела още може да се нарече “вертикален модел за представяне”, докато табличния е “хоризонтален”. Примерно записът “Ситроен С5” може да се представи с: “тип” = “хечбек”, “гориво” – “дизел”, “обем двигател” = “2200”, “скоростна кутия” = “автоматична” и т.н. В една релационна таблица това би изглеждало така:

+------------+--------+---------------+-----------------+
|    Име     | Гориво | Обем двигател | Скоростна кутия |
+------------+--------+---------------+-----------------+
| Ситроен С5 | Дизел  |          2200 | Автоматична     |
| VW Golf    | Бензин |          1600 | Ръчна           |
+------------+--------+---------------+-----------------+

Докато в един EAV модел представен таблично би изглеждало така (вижда се и защо се казва “вертикално представяне”):

+-------+-----------------+-------------+
| Запис |    Свойство     |  Стойност   |
+-------+-----------------+-------------+
|     1 | Име             | Ситроен С5  |
|     1 | Гориво          | Дизел       |
|     1 | Обем двигател   | 2200        |
|     1 | Скоростна кутия | Автоматична |
|     2 | Име             | Vw Golf     |
|     2 | Гориво          | Бензин      |
|     2 | Обем двигател   | 1600        |
|     2 | Скоростна кутия | Ръчна       |
+-------+-----------------+-------------+

Колоната “Запис” трябва да групира логически двойките в отделни записи.

Защо би се приискало на някой да използва EAV? Лесно се добавят нови полета, схемата на данните е отворена. Ако на късен етап от живота на нашия проект се окаже че за колите трябва да се пазят броя на гумите, просто въвеждаме ново свойство: “брой гуми” = “4”. Всичко е там – можем да разграничим отделните записи, можем да добавяме абсолютно произволни данни, можем да взимаме кой да е запис с всичките му полета. Ако ни потрябват всичките коли с обем на двигателя над 2000 съвсем лесно можем да вземем този списък. Звучи като песни и рози, особено ако трябва да пазите много различни типове документи и не ви се занимава с MySQL таблици от по 30-40-50 полета. Ако мислите така, значи сте с двата крака в капана.

Ако все още четете това и имате чувството че това четиво е насочено за вас – никога, ама никога не правете EAV в MySQL. Ако ви трябва за нещо елементарно (примерно – user preferences) – става, но ако имате голяма система, която има тенденция да се развива – не използвайте EAV.

MySQL е система за релационни бази данни. EAV не е релационен модел. Данните представени в EAV не са нормализирани.
Някои от важните features които липсват в EAV:
1. Метаданни. Не знаете кое поле какъв тип е. Нямате допустими стойности за полетата. Нямате валидиране на данните. DB енджина ще допусне да въведете “джинджипляктор” в стойността на “Обем двигател”. Не можете да използвате MySQL-ските типове данни по колоните.
2. Свързаност между записите. Няма foreign keys. Ако искате да намерите шофьора на дадена кола, ще трябва да правите self join на таблицата, вложени SELECT-и или две последователни заявки. Ако искате да намерите жената на шофьора на дадена кола, може да си направите харакири.
3. Индексиране. Данните в колоната “Стойност” са разнородни не могат да бъдат индексирани по смисъл.
4. Подреденост – няма колона по която могат да се подредят данните.
5. Странициране – по какъв критерий ще страницирате?
6. Агрегатни функции – не могат да се използват директно.

Как да филтрираме записа който ни трябва? Ако ни трябват всички коли с обем на двигателя над 2000 в релационна таблица това би било така:

SELECT id FROM cars 
WHERE engine_volume > 2000

В EAV таблица е почти същото:

SELECT id FROM fields 
WHERE attribute = 'engine_volume' AND value > 2000

Ако искаме да вземем само дизеловите автомобили с марка ситроен? В релационна таблица е елементарно:

SELECT id FROM cars 
WHERE fuel = 'diesel' AND brand = 'citroen'

В EAV може би ще го направим така:

SELECT id FROM fields 
WHERE (attribute = 'brand' AND value = 'citroen') 
    AND (attribute = 'fuel' AND value = 'diesel')

Да, ама не. Така написана заявката няма да върне нищо. Никога няма ЕДИН ред в който едновременно да има атрибут ‘brand’ и атрибут ‘fuel’. Може да пробваме така:

SELECT id FROM fields a
    INNER JOIN fields b
    ON a.id = b.id
WHERE 
    (a.attribute = 'brand' AND a.value = 'citroen')
AND
    (b.attribute = 'fuel' AND b.value = 'diesel')

Таблицата fields обикновено е огромна (все пак всяко поле е отделен ред) и си представете какво става при JOIN на огромна таблица със самата себе си… А ако търсим по три критерия? Три JOIN-a.
Алтернатива:

SELECT id FROM fields
WHERE value IN ('citroen', 'diesel') 
GROUP BY id
HAVING COUNT(*) = 2

Това може да работи ако търсените стойности са определени, но не и ако се търси нещо от вида “по-голямо”, “по-малко”, “съдържа низа”.

EAV е пример как “данните са там, ама не ни вършат работа”. Оказва се че основните MySQL функционалности трябва да се имплементират програмно и докато се усетим вече си пишем собствен DB енджин… Има ситуации в които наистина е необходим отворен дизайн на базата – в такъв случай съществуват NoSQL решения, които са създадени точно за това.