Czy istnieje nazwa dla tego schematu bazy danych kluczowych wartości?

Przetwarzamy rutynowy plik danych od klienta, który właśnie dokonał refaktoryzacji swojej bazy danych z formularza, który wydaje się znajomy (jeden wiersz na jednostkę, jedną kolumnę na atrybut) do jednego, który wydaje mi się nieznany (jeden wiersz na jednostkę na atrybut):

Przed: jedna kolumna na atrybut

ID   Ht_cm   wt_kg   Age_yr  ... 
1      190      82     43    ...
2      170      60     22    ...
3      205      90     51    ...

Po: jedna kolumna dla wszystkich atrybutów

ID    Metric   Value
 1     Ht_cm     190
 1     Wt_kg     82
 1     Age_yr    43
 1      ...
 2     Ht_cm     170
 2     Wt_kg     60
 2     Age_yr    22
 2     ...
 3     Ht_cm     205
 3     Wt_kg     90
 3     Age_yr    51
 3     ...

Czy istnieje nazwa dla tej struktury bazy danych? Jakie są względne zalety? Stary sposób wydaje się łatwiejszy do nałożenia ograniczeń poprawności na określone atrybuty (niepuste, nieujemne itp.) I łatwiejszy do obliczenia średnich. Ale widzę, jak łatwiej byłoby dodać nowe atrybuty bez refaktoryzacji bazy danych. Czy to standardowy / preferowany sposób strukturyzacji danych?

Nazywa się to Entity-Attribute-Value (czasami także „parami nazwa-wartość”) i jest to klasyczny przypadek „okrągłego kołka w kwadratowym otworze”, gdy ludzie używają wzorca EAV w relacyjnej bazie danych.

Oto lista powodów, dla których nie należy używać EAV:

• Nie możesz używać typów danych. Nie ma znaczenia, czy wartością jest data, liczba, czy pieniądze (dziesiętne). Zawsze będzie zmuszony do varchar. Może to być coś od drobnego problemu z wydajnością do ogromnego bólu jelit (czy kiedykolwiek musiałeś ścigać różnicę jednego centa w miesięcznym raporcie podsumowującym?).
• Nie możesz (łatwo) egzekwować ograniczeń. Wymaga to absurdalnej ilości kodu, aby wymusić „Każdy musi mieć wysokość od 0 do 3 metrów” lub „Wiek nie może być zerowy i> = 0”, w przeciwieństwie do 1-2 linii, że każde z tych ograniczeń byłoby w prawidłowo wymodelowanym systemie.
• W związku z powyższym nie możesz łatwo zagwarantować, że otrzymasz informacje potrzebne każdemu klientowi (w jednym z nich może brakować wieku, a w innym może nie być wzrostu, itp.). Możesz to zrobić, ale jest o wiele trudniejsze niż SELECT height, weight, age FROM Client where height is null or weight is null.
• Powiązane ponownie, zduplikowane dane są znacznie trudniejsze do wykrycia (co się stanie, jeśli dadzą ci dwa lata dla jednego klienta? Usunięcie EAV danych, jak poniżej, da ci dwa wiersze wyników, jeśli masz jeden atrybut podwójny. Jeśli jeden klient ma dwa osobne wpisy dla dwóch atrybutów, otrzymasz cztery wiersze z zapytania poniżej).
• Nie możesz nawet zagwarantować, że nazwy atrybutów są spójne. „Age_yr” może być „AGE_IN_YEARS” lub „age”. (Trzeba przyznać, że jest to mniejszy problem, gdy otrzymujesz wyciąg, a kiedy ludzie wstawiają dane, ale nadal.)
• Wszelkie nietrywialne zapytania to kompletna katastrofa. Aby relacjonować trójwymiarowy system EAV w celu racjonalnego zapytania go, wymagane są trzy sprzężenia tabeli EAV.

Porównać:

SELECT cID.ID AS [ID], cH.Value AS [Height], cW.Value AS [Weight], cA.Value AS [Age]
FROM (SELECT DISTINCT ID FROM Client) cID 
      LEFT OUTER JOIN 
    Client cW ON cID.ID = cW.ID AND cW.Metric = "Wt_kg" 
      LEFT OUTER JOIN 
    Client cH ON cID.ID = cH.ID AND cW.Metric = "Ht_cm" 
      LEFT OUTER JOIN 
    Client cA ON cID.ID = cA.ID AND cW.Metric = "Age_yr"

Do:

SELECT c.ID, c.Ht_cm, c.Wt_kg, c.Age_yr
FROM Client c

Oto (bardzo krótka) lista, kiedy powinieneś używać EAV:

• Gdy nie ma absolutnie żadnej możliwości obejścia tego problemu i musisz obsługiwać dane bez schematu w bazie danych.
• Gdy po prostu potrzebujesz przechowywać „rzeczy” i nie oczekujesz, że będziesz potrzebować ich w bardziej uporządkowanej formie. Uważaj jednak, potwór nazwał „zmieniającymi się wymaganiami”.

Wiem, że właśnie spędziłem cały ten post opisując, dlaczego EAV jest okropnym pomysłem w większości przypadków - ale jest kilka przypadków, w których jest to konieczne / nieuniknione. jednak przez większość czasu (w tym powyższy przykład) będzie to znacznie trudniejsze niż warte. Jeśli masz potrzebę szerokiej obsługi danych typu EAV, powinieneś rozważyć przechowywanie ich w systemie klucz-wartość, np. Hadoop / HBase, CouchDB, MongoDB, Cassandra, BerkeleyDB.

Wartość atrybutu jednostki (EAV)

Jest uważany za anty-wzór przez wielu, w tym przeze mnie.

Oto twoje alternatywy:

1. użyj dziedziczenia tabeli bazy danych

2. używać danych XML i funkcji SQLXML

3. użyj bazy danych nosql, takiej jak HBase

W PostgreSQL jednym bardzo dobrym sposobem radzenia sobie ze strukturami EAV jest dodatkowy moduł hstore, dostępny dla wersji 8.4 lub nowszej. Cytuję instrukcję:

Ten moduł implementuje hstoretyp danych do przechowywania zestawów par klucz / wartość w obrębie jednej wartości PostgreSQL. Może to być przydatne w różnych scenariuszach, takich jak wiersze z wieloma atrybutami, które są rzadko badane, lub częściowo ustrukturyzowane dane. Klucze i wartości są po prostu ciągami tekstowymi.

Od wersji Postgres 9.2 dostępny jest także jsontyp i mnóstwo funkcji ( większość z nich dodana wraz z 9.3 ).

Postgres 9.4 dodaje (o wiele lepszy!) Typ danych „binarny JSON” jsonbdo listy opcji. Z zaawansowanymi opcjami indeksu.

Jeśli masz bazę danych korzystającą ze struktury EAV, możliwe jest zapytanie danych na różne sposoby.

@ Odpowiedź Simona już pokazuje, jak wykonać zapytanie przy użyciu wielu sprzężeń.

Przykładowe użyte dane:

CREATE TABLE yourtable ([ID] int, [Metric] varchar(6), [Value] int);

INSERT INTO yourtable ([ID], [Metric], [Value])
VALUES (1, 'Ht_cm', 190),
    (1, 'Wt_kg', 82),
    (1, 'Age_yr', 43),
    (2, 'Ht_cm', 170),
    (2, 'Wt_kg', 60),
    (2, 'Age_yr', 22),
    (3, 'Ht_cm', 205),
    (3, 'Wt_kg', 90),
    (3, 'Age_yr', 51);

Jeśli używasz RDBMS, który ma PIVOTfunkcję ( SQL Server 2005+ / Oracle 11g + ), możesz zapytać o dane w następujący sposób:

select id, Ht_cm, Wt_kg, Age_yr
from
(
  select id, metric, value
  from yourtable
) src
pivot
(
  max(value)
  for metric in (Ht_cm, Wt_kg, Age_yr)
) piv;

Zobacz SQL Fiddle with Demo

Jeśli nie masz dostępu do PIVOTfunkcji, możesz użyć funkcji agregującej z CASEinstrukcją, aby zwrócić dane:

select id,
  max(case when metric ='Ht_cm' then value else null end) Ht_cm,
  max(case when metric ='Wt_kg' then value else null end) Wt_kg,
  max(case when metric ='Age_yr' then value else null end) Age_yr
from yourtable
group by id

Zobacz SQL Fiddle with Demo

Oba te zapytania zwrócą dane w wyniku:

| ID | HT_CM | WT_KG | AGE_YR |
-------------------------------
|  1 |   190 |    82 |     43 |
|  2 |   170 |    60 |     22 |
|  3 |   205 |    90 |     51 |

Zabawnie jest widzieć, jak krytykowany jest model db EAV, a nawet przez niektórych uważany za „anty-wzór”.

Według mnie najważniejsze wady to:

• Krzywa uczenia się jest bardziej stroma, jeśli trafisz na projekt, który już jakiś czas temu zaczął używać EAV. Rzeczywiście, zapytania są trudne, ponieważ znacznie zwiększasz liczbę złączeń (i tabel), więc potrzeba więcej czasu na zrozumienie. Wystarczy rzucić okiem na projekt Magento i zobaczyć, jak deweloper zewnętrzny projektu ma trudności z pracą nad DB, ale dokumentacja jest dobrze utrzymana.
• Nie nadaje się do raportowania , jeśli chcesz uzyskać liczbę osób, których imię zaczyna się na „M” itp.

Jednak zdecydowanie nie należy odrzucać tego rozwiązania, a oto dlaczego:

• Simon mówił o potworze zwanym „zmieniającymi się wymaganiami”. Podoba mi się to wyrażenie :). I IMHO właśnie dlatego EAV może być dobrym kandydatem, ponieważ dobrze nadaje się do „zmiany” , ponieważ możesz łatwo dodać tyle atrybutów, ile chcesz. Oczywiście zależy to od zmieniających się wymagań. Jeśli mówimy o zupełnie nowej firmie, oczywiście będziesz musiał przejrzeć swój model danych, ale EAV oferuje dużą elastyczność. To, że wymaga większej dyscypliny, nie oznacza, że ​​jest to mniej interesujące.
• Mówiono również, że „nie można używać typów danych”. : To źle . Równie dobrze możesz mieć kilka tabel wartości , po jednej dla każdego typu danych. Następnie musisz określić w tabeli atrybutów, jaki rodzaj danych to Twój atrybut. W rzeczywistości połączenie klasycznej relacji / relacji EAV z relacją klas oferuje wiele interesujących możliwości w zakresie projektowania bazy danych.