Jak zaprojektować bazę danych dla pól zdefiniowanych przez użytkownika?

Mam napisane o tym problemie dużo . Najczęstszym rozwiązaniem jest Antypattern Encja-atrybut-wartość, który jest podobny do tego, co opisujesz w opcji # 3. unikaj tego projektu jak zarazy .

To, czego używam do tego rozwiązania, gdy potrzebuję naprawdę dynamicznych niestandardowych pól, to przechowywanie ich w blobie XML, dzięki czemu mogę dodawać nowe pola w dowolnym momencie. Ale aby było to szybkie, Utwórz również dodatkowe tabele dla każdego pola, które musisz przeszukać lub posortować (ty nie ma tabeli na pole-tylko tabelę na pole przeszukiwalne ). Jest to czasami nazywane odwróconym projektem indeksu.

Ciekawy artykuł z 2009 roku o tym rozwiązaniu można przeczytać tutaj: http://backchannel.org/blog/friendfeed-schemaless-mysql

LUB możesz użyć bazy danych zorientowanej na dokument, gdzie oczekuje się, że masz niestandardowe pola dla każdego dokumentu. Wybrałbym Solr .

Najprawdopodobniej stworzyłbym tabelę o następującej strukturze:

• Nazwa varchar
• Typ varchar
• Liczba dziesiętna
• VARCHAR StringValue
• Data DateValue

Dokładne typy kursów zależą od twoich potrzeb (i oczywiście od dbms, którego używasz). Można również użyć pola NumberValue (decimal) dla int i booleans. Możesz potrzebować również innych typów.

Potrzebujesz jakiegoś linka do rekordów Głównych, które posiadają wartość. Prawdopodobnie najłatwiej i najszybciej jest utworzyć tabelę pól użytkownika dla każdej tabeli głównej i dodać prosty klucz obcy. W ten sposób można łatwo i szybko filtrować rekordy główne według pól użytkownika.

Możesz chcieć mieć jakieś metadane. Więc kończysz z następującym:

Tabela UdfMetaData

• int id
• Nazwa varchar
• Typ varchar

Tabela MasterUdfValues

• Int Master_FK
• int MetaData_FK
• Liczba dziesiętna
• VARCHAR StringValue
• Data DateValue

Cokolwiek zrobisz, nie zmieniałbym dynamicznie struktury tabeli. To koszmar konserwacyjny. Chciałbym również nie używać struktur XML, są one o wiele za wolne.

To brzmi jak problem, który może być lepiej rozwiązany przez rozwiązanie nie relacyjne, takie jak MongoDB lub CouchDB.

Oba pozwalają na dynamiczne Rozszerzanie schematu, jednocześnie pozwalając na zachowanie integralności krotki, której szukasz.

Zgadzam się z Billem Karwinem, model EAV nie jest dla Ciebie skutecznym podejściem. Używanie par nazwa-wartość w systemie relacyjnym nie jest z natury złe, ale działa dobrze tylko wtedy, gdy para nazwa-wartość tworzy kompletną krotkę informacji. Podczas korzystania z niego zmusza do dynamicznej rekonstrukcji stołu w czasie biegu, wszystkie rodzaje rzeczy zaczynają być trudne. Querying staje się ćwiczeniem w konserwacji pivot lub zmusza do wciśnięcia rekonstrukcji krotki do warstwy obiektu.

Bez osadzania reguł schematu w warstwie obiektu nie można określić, czy wartość null lub brakująca jest prawidłowym wpisem, czy też brakiem wpisu.

Tracisz możliwość efektywnego zarządzania schematem. Czy 100-znakowy varchar jest właściwym typem dla pole "wartość"? 200 znaków? Czy to powinno być nvarchar zamiast? Może to być trudny kompromis i taki, który kończy się koniecznością nałożenia sztucznych ograniczeń na dynamiczny charakter zestawu. Coś w stylu " możesz mieć tylko X zdefiniowane przez użytkownika pola i każdy może mieć tylko y znaków.

W przypadku rozwiązania zorientowanego na dokument, takiego jak MongoDB lub CouchDB, wszystkie atrybuty powiązane z użytkownikiem są zachowywane w ramach jednej krotki. Ponieważ nie jest to problem, życie jest szczęśliwe, ponieważ żadna z tych dwa dobrze sobie radzą z łącznikami, mimo szumu. Twoi użytkownicy mogą zdefiniować tyle atrybutów ,ile chcą (lub pozwolisz) w długościach, które nie stają się trudne do zarządzania, dopóki nie osiągniesz około 4MB.

Jeśli masz dane, które wymagają integralności na poziomie kwasowym, możesz rozważyć podział rozwiązania, z danymi o wysokiej integralności żyjącymi w relacyjnej bazie danych i danymi dynamicznymi żyjącymi w sklepie nie relacyjnym.

Nawet jeśli użytkownik dodaje własne kolumny, nie musi być tak, że Zapytanie o te kolumny będzie dobrze działać. Istnieje wiele aspektów, które wchodzą w projektowanie zapytań, które pozwalają im dobrze działać, z których najważniejszym jest odpowiednia specyfikacja tego, co powinno być przechowywane w pierwszej kolejności. Tak więc, zasadniczo, jest to, że chcesz umożliwić użytkownikom tworzenie schematu bez myślenia o specyfikacjach i być w stanie szybko czerpać informacje z tego schemat? Jeśli tak, to jest mało prawdopodobne, że każde takie rozwiązanie będzie dobrze skalowane, zwłaszcza jeśli chcesz pozwolić użytkownikowi na analizę numeryczną danych.

Opcja 1

IMO takie podejście daje schemat bez wiedzy co oznacza schemat, który jest receptą na katastrofę i koszmarem dla projektantów raportów. Czyli musisz mieć metadane, aby wiedzieć, która kolumna przechowuje jakie dane. Jeśli te metadane się pomieszają, może to spowodować wyciek danych. Plus, to sprawia, że łatwo umieścić złe dane w niewłaściwej kolumnie. ("Co? String1 zawiera nazwę klasztorów? Myślałem, że to ulubione narkotyki Chalie Sheen.")

Opcja 3,4,5

IMO, wymagania 2, 3 i 4 eliminują wszelkie zmiany EAV. Jeśli potrzebujesz odpytywać, sortować lub wykonywać obliczenia na tych danych, EAV jest marzeniem Cthulhu i koszmarem Twojego zespołu programistów i DBA. EAV stworzy wąskie gardło pod względem wydajności i nie zapewni integralności danych, której potrzebujesz szybko dostać się do informacji, które chcesz. Zapytania szybko zamienią się w węzły crosstab Gordian.

Opcja 2,6

To naprawdę pozostawia jeden wybór: zebrać specyfikacje, a następnie zbudować schemat.

Jeśli klient chce uzyskać najlepszą wydajność na danych, które chce przechowywać, musi przejść proces współpracy z deweloperem, aby zrozumieć jego potrzeby, aby były przechowywane tak wydajnie, jak to możliwe. Nadal może być przechowywany w tabeli oddzielonej od reszty tabele z kodem, który dynamicznie buduje formularz na podstawie schematu tabeli. Jeśli masz bazę danych, która pozwala na rozszerzone właściwości kolumn, możesz nawet użyć tych, aby pomóc konstruktorowi formularzy używać ładnych etykiet, podpowiedzi itp. tak, że wszystko, co było konieczne jest dodanie schematu. Tak czy inaczej, aby skutecznie tworzyć i uruchamiać raporty, dane muszą być odpowiednio przechowywane. Jeśli dane będą miały wiele null, niektóre bazy danych mają możliwość przechowywania tego typu informacji. Na przykład, SQL Server 2008 ma funkcję o nazwie Sparse Columns specjalnie dla danych z dużą ilością null.

Gdyby to był tylko worek danych, na których nie trzeba było analizować, filtrować ani sortować, powiedziałbym, że jakaś odmiana podsłuchu może załatwić sprawę. Jednak, biorąc pod uwagę twoje wymagania, najbardziej efektywnym rozwiązaniem będzie uzyskanie odpowiednich specyfikacji, nawet jeśli będziesz przechowywać te nowe kolumny w oddzielnych tabelach i dynamicznie budować formularze z tych tabel.

Sparse Kolumny

1. Tworzenie wielu tabel UDF, po jednej dla każdego typu danych. Więc mamy tabele dla UDFStrings, UDFDates, itp. Prawdopodobnie zrobi to samo co #2 i automatycznie wygeneruje Widok za każdym razem, gdy nowe pole zostanie dodane

Według moich badań wiele tabel opartych na typie danych nie pomoże Ci w wydajności. Zwłaszcza jeśli masz dane zbiorcze, takie jak 20K lub 25k rekordów z 50 + UDFs. Wyniki były najgorsze.

Należy wybrać jedną tabelę z wieloma kolumny jak:

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

Jest to problematyczna sytuacja, A żadne z rozwiązań nie wydaje się"właściwe". Jednak wariant 1 jest prawdopodobnie najlepszy zarówno pod względem prostoty, jak i wydajności.

Jest to również rozwiązanie stosowane w niektórych komercyjnych aplikacjach korporacyjnych.

EDIT

Inną opcją, która jest teraz dostępna, ale nie istniała (lub przynajmniej nie była dojrzała), gdy pytanie było oryginalne, jest użycie pól json w DB.

Wiele relacyjnych DBs obsługuje teraz pola oparte na json (które mogą zawierać dynamiczną listę pól podrzędnych) i zezwalać na ich odpytywanie

Postgress

Mysql

Miałem doświadczenie lub 1, 3 i 4 i wszystkie kończą się albo bałaganem, z tym, że nie jest jasne, czym są dane, albo naprawdę skomplikowanym rodzajem miękkiej kategoryzacji, aby podzielić dane na dynamiczne typy rekordów.

Skusiłbym się na wypróbowanie XML, powinieneś być w stanie wymusić Schematy względem zawartości xml, aby sprawdzić typowanie danych itp., co pomoże trzymać zestawy różnic danych UDF. W nowszych wersjach SQL server można indeksować na polach XML, co powinno pomóc na występie. (patrz http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx) na przykład

Jeśli używasz SQL Server, nie przeocz typu sqlvariant. Jest dość szybki i powinien wykonać swoją pracę. Inne bazy danych mogą mieć coś podobnego.

Typy danych XML nie są tak dobre ze względu na wydajność. Jeśli robisz obliczenia na serwerze, to ciągle musisz je deserializować.

Opcja 1 brzmi źle i wygląda paskudnie, ale pod względem wydajności może być najlepszym rozwiązaniem. Stworzyłem wcześniej tabele z kolumnami o nazwie Field00-Field99, ponieważ po prostu nie można pokonaj występ. Być może trzeba również wziąć pod uwagę wydajność wkładki, w którym to przypadku jest to również ten, do którego należy się udać. Zawsze możesz tworzyć widoki na tym stole, jeśli chcesz, aby wyglądał schludnie!

Udało mi się to bardzo pomyślnie w przeszłości, używając żadnej z tych opcji (opcja 6? :) ).

Tworzę model dla użytkowników do zabawy (przechowywać jako xml i wystawiać za pomocą niestandardowego narzędzia do modelowania) i z modelu generowane tabele i widoki do łączenia tabel bazowych z tabel danych zdefiniowanych przez użytkownika. Tak więc każdy typ będzie miał tabelę bazową z podstawowymi danymi i tabelę użytkownika z polami zdefiniowanymi przez użytkownika.

Weźmy dokument jako przykład: typowe pola to nazwa, typ, data, autor itp. To trafiłoby do podstawowej tabeli. Następnie użytkownicy definiowaliby własne specjalne typy dokumentów z własnymi polami, takimi jak contract_end_date, renewal_clause, bla bla bla. Dla tego zdefiniowanego przez użytkownika dokumentu istnieje podstawowa tabela dokumentu, tabela xcontract, połączona na wspólnym kluczu głównym(tak więc klucz podstawowy xcontracts jest również obcy na kluczu głównym tabeli core). Następnie chciałbym wygenerować widok do zawijania tych dwóch tabel. Wydajność podczas zapytań była szybka. dodatkowe reguły biznesowe mogą być również osadzone w widokach. Dobrze mi to wyszło.

SharePoint używa opcji 1 i ma rozsądną wydajność.

W komentarzach widziałem, jak mówiłeś, że pola UDF mają zrzucać importowane dane, które nie są poprawnie odwzorowane przez użytkownika.

Być może inną opcją jest śledzenie liczby UDF wykonanych przez każdego użytkownika i zmuszanie go do ponownego użycia pól, mówiąc, że może używać 6 (lub innych równie losowych limitów) pól niestandardowych.

Gdy masz do czynienia z problemem struktury bazy danych, jak ten, często najlepiej jest wrócić do podstawowego projektu aplikacji (system importu w Twoim przypadku) i jeszcze kilka ograniczeń.

Teraz zrobiłbym opcję 4 (EDIT) z dodaniem linku do użytkowników:

general_data_table
id
...


udfs_linked_table
id
general_data_id
udf_id


udfs_table
id
name
type
owner_id --> Use this to filter for the current user and limit their UDFs
string_link_id --> link table for string fields
int_link_id
type_link_id

Teraz upewnij się, aby widoki zoptymalizować wydajność i uzyskać swoje indeksy prawo. Ten poziom normalizacji sprawia, że dB footprint jest mniejszy, ale Twoja aplikacja jest bardziej złożona.

Nasza baza danych zasila aplikację SaaS (oprogramowanie helpdesk), w której użytkownicy mają ponad 7k "niestandardowych pól". Stosujemy podejście kombinowane:

1. (EntityID, FieldID, Value) tabela dla przeszukiwania danych
2. pole JSON w tabeli entities, które zawiera wszystkie wartości encji, używane do wyświetlania danych. (w ten sposób nie potrzebujesz miliona JOIN ' ów, aby uzyskać wartości wartości).

Możesz dalej podzielić #1, aby mieć "tabelę na typ danych", jak ta odpowiedź sugeruje, w ten sposób możesz nawet indeksować swoje UDFs.

P. S. kilka słów do obrony podejścia "Entity-Attribute-Value" wszyscy ciągle walczą. Używaliśmy #1 Bez #2 od dziesięcioleci i działało dobrze. Czasami to decyzja biznesowa. Czy masz czas, aby przepisać swoją aplikację i przeprojektować db lub można przez kilka dolarów na chmurze-serwery, które są naprawdę tanie w dzisiejszych czasach? Nawiasem mówiąc, kiedy używaliśmy podejścia #1, nasz DB trzymał miliony jednostek, dostęp do 100 tysięcy użytkowników, a dwurdzeniowy serwer db 16GB radził sobie dobrze (naprawdę "R3" vm na AWS).