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


145

Moje wymagania to:

  • Trzeba mieć możliwość dynamicznego dodawania pól zdefiniowanych przez użytkownika dowolnego typu danych
  • Trzeba mieć możliwość szybkiego wysyłania zapytań do UDF
  • Musisz mieć możliwość wykonywania obliczeń na UDF na podstawie typu danych
  • Trzeba mieć możliwość sortowania UDF na podstawie typu danych

Inne informacje:

  • Szukam przede wszystkim wydajności
  • Istnieje kilka milionów rekordów głównych, do których można dołączyć dane UDF
  • Kiedy ostatnio sprawdzałem, w naszej aktualnej bazie danych było ponad 50 milionów rekordów UDF
  • W większości przypadków UDF jest przypisany tylko do kilku tysięcy rekordów Master, a nie do wszystkich
  • UDF nie są łączone ani używane jako klucze. To tylko dane używane do zapytań lub raportów

Opcje:

  1. Utwórz dużą tabelę za pomocą StringValue1, StringValue2 ... IntValue1, IntValue2, ... itd. Nienawidzę tego pomysłu, ale rozważę go, jeśli ktoś może mi powiedzieć, że jest lepszy niż inne pomysły i dlaczego.

  2. Utwórz dynamiczną tabelę, która w razie potrzeby dodaje nową kolumnę na żądanie. Nie podoba mi się również ten pomysł, ponieważ uważam, że wydajność byłaby wolna, gdyby nie zindeksowanie każdej kolumny.

  3. Utwórz pojedynczą tabelę zawierającą UDFName, UDFDataType i wartość. Po dodaniu nowego UDF wygeneruj widok, który pobiera tylko te dane i analizuje je na dowolny określony typ. Elementy, które nie spełniają kryteriów analizy, zwracają wartość NULL.

  4. Utwórz wiele tabel UDF, po jednej na typ danych. Więc mielibyśmy tabele dla UDFStrings, UDFDates, itp. Prawdopodobnie zrobiłoby to samo co # 2 i automatycznie wygenerowałoby widok za każdym razem, gdy zostanie dodane nowe pole

  5. XML DataType? Nie pracowałem z nimi wcześniej, ale widziałem je wspomniane. Nie jestem pewien, czy dałyby mi oczekiwane wyniki, zwłaszcza jeśli chodzi o wydajność.

  6. Coś innego?


7
Martin Fowler zaleca 2 (schemat do aktualizacji przez użytkownika) lub 5 (indeksowany XML LOB): martinfowler.com/bliki/UserDefinedField.html
Neil McGuigan

Zobacz także pytanie StackOverflow dotyczące schematów dynamicznej bazy danych .
FloverOwe

Odpowiedzi:


49

Jeśli wydajność jest głównym problemem, wybrałbym # 6 ... tabelę na UDF (tak naprawdę jest to wariant # 2). Ta odpowiedź jest specjalnie dostosowana do tej sytuacji i opisu dystrybucji danych i opisanych wzorców dostępu.

Plusy:

  1. Ponieważ wskażesz, że niektóre UDF mają wartości dla niewielkiej części ogólnego zestawu danych, oddzielna tabela zapewni najlepszą wydajność, ponieważ ta tabela będzie tylko tak duża, jak to konieczne, aby obsługiwać UDF. To samo dotyczy powiązanych indeksów.

  2. Zyskujesz również przyspieszenie, ograniczając ilość danych, które muszą być przetwarzane w celu agregacji lub innych przekształceń. Dzielenie danych na wiele tabel umożliwia wykonanie niektórych agregacji i innych analiz statystycznych na danych UDF, a następnie połączenie wyniku z tabelą główną za pomocą klucza obcego w celu uzyskania atrybutów niezagregowanych.

  3. Możesz użyć nazw tabel / kolumn, które odzwierciedlają rzeczywiste dane.

  4. Masz pełną kontrolę nad używaniem typów danych, sprawdzaniem ograniczeń, wartości domyślnych itp. W celu definiowania domen danych. Nie lekceważ wydajności wynikającej z konwersji typu danych w locie. Takie ograniczenia pomagają również optymalizatorom zapytań RDBMS opracowywać bardziej efektywne plany.

  5. Jeśli kiedykolwiek zajdzie potrzeba użycia kluczy obcych, wbudowana deklaratywna integralność referencyjna jest rzadko wykonywana przez wymuszanie ograniczeń na poziomie wyzwalacza lub aplikacji.

Cons:

  1. Może to spowodować utworzenie wielu tabel. Wymuszenie separacji schematu i / lub konwencji nazewnictwa mogłoby to złagodzić.

  2. Do obsługi definicji UDF i zarządzania nim potrzeba więcej kodu aplikacji. Spodziewam się, że jest to nadal mniej potrzebnego kodu niż w przypadku oryginalnych opcji 1, 3 i 4.

Inne uwagi:

  1. Jeśli jest cokolwiek w naturze danych, które miałoby sens dla grupowania UDF, należy do tego zachęcać. W ten sposób te elementy danych można połączyć w jedną tabelę. Na przykład, powiedzmy, że masz UDF dotyczące koloru, rozmiaru i kosztu. Dane są takie, że większość wystąpień tych danych wygląda jak

     'red', 'large', 45.03 

    zamiast

     NULL, 'medium', NULL

    W takim przypadku nie poniesiesz zauważalnej kary za szybkość, łącząc 3 kolumny w 1 tabeli, ponieważ kilka wartości będzie równych NULL i unikniesz tworzenia 2 kolejnych tabel, czyli 2 mniej złączeń potrzebnych, gdy potrzebujesz dostępu do wszystkich 3 kolumn .

  2. Jeśli trafisz na ścianę wydajności z UDF, który jest mocno zapełniony i często używany, należy to rozważyć, aby uwzględnić go w tabeli głównej.

  3. Logiczne projektowanie tabel może doprowadzić Cię do pewnego punktu, ale kiedy liczba rekordów stanie się naprawdę ogromna, powinieneś również zacząć przyglądać się, jakie opcje partycjonowania tabel zapewnia wybrany RDBMS.


1
Listy kontrolne! Wewnętrzny żart między mną a Philem, mam nadzieję, że nie jest to sprzeczne z zasadami.
GunnerL3510

Dzięki, myślę, że zrobię jakąś wariację tego. Większość naszych danych UDF pochodzi z niezamapowanych pól importu, które muszą pozostać tylko w celach informacyjnych, więc chciałbym umieścić je w jednej tabeli. Inne UDF są definiowane zgodnie z potrzebami (nie mogę ich z góry zidentyfikować. Zwykle są tworzone, gdy zmieniamy jakiś proces lub decydujemy się śledzić coś specjalnego przez kilka miesięcy) i są powszechnie używane w zapytaniach. Myślę, że utworzę oddzielną tabelę dla każdej jednostki logicznej tych wartości.
Rachel,

Pracuję z tabelą, która ma datowane / wersjonowane UDF, używam tej metody, stackoverflow.com/a/123481/328968 , aby uzyskać najnowsze wartości.
Peter,

22

Mam napisane o tym problemie a lot . Najczęstszym rozwiązaniem jest anticattern Entity-Attribute-Value, który jest podobny do tego, co opisujesz w swojej opcji nr 3. Unikaj tego projektu jak zarazy .

To, czego używam w przypadku tego rozwiązania, gdy potrzebuję prawdziwie dynamicznych pól niestandardowych, to przechowywanie ich w postaci blobu XML, aby móc dodawać nowe pola w dowolnym momencie. Aby jednak przyspieszyć, utwórz dodatkowe tabele dla każdego pola, według którego chcesz przeszukiwać lub sortować (nie ma tabeli na pole - tylko tabela na pole z możliwością wyszukiwania ). Nazywa się to czasem odwróconym projektem indeksu.

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

Lub możesz użyć bazy danych zorientowanej na dokumenty, w której oczekuje się, że masz niestandardowe pola na dokument. Chciałbym wybrać Solr .


1
Czy możesz wyjaśnić, dlaczego powinienem unikać opcji nr 3? Przyjrzałem się niektórym z twoich przykładów, ale tak naprawdę nie są one tym samym, co próbuję zrobić. Po prostu chcę mieć miejsce do przechowywania dodatkowych danych, a nie miejsce do przechowywania wszystkich atrybutów.
Rachel

2
Na początek, dla kogo atrybut NIE ZEROWY? Jak uczynić atrybut UNIKATOWYM, nie czyniąc wszystkich atrybutów WYJĄTKOWYMI? To trwa od tego momentu. W końcu piszesz kod aplikacji, aby zapewnić funkcje, które RDBMS już zapewnia, nawet do tego stopnia, że ​​musisz napisać jakąś klasę mapowania, aby po prostu wstawić logiczny rekord jednostki i odzyskać go.
Bill Karwin,

2
Krótka odpowiedź brzmi: „nie mieszaj danych i metadanych”. Tworzenie kolumn varchar dla fieldnamelub tablenameprzechowuje identyfikatory metadanych jako ciągi danych i to jest początek wielu problemów. Zobacz także en.wikipedia.org/wiki/Inner-platform_effect
Bill Karwin

2
@Thomas: W odwróconym projekcie indeksu można używać standardowych rozwiązań schematu dla typów danych i ograniczeń, takich jak UNIQUE i FOREIGN KEY. Te w ogóle nie działają, gdy używasz EAV. Zgadzam się, że odwrócone udziały indeksu z EAV są nierelacyjne tylko dlatego, że obsługują różne atrybuty na wiersz, ale jest to kwestia kompromisu.
Bill Karwin,

2
@thitami, Przez lata nauczyłem się, że każde rozwiązanie może być odpowiednie dla Twojej aplikacji. Nawet EAV może być najmniej złym rozwiązaniem dla niektórych aplikacji. Nie możesz wybrać strategii optymalizacji bez znajomości zapytań. Każdy rodzaj optymalizacji poprawia niektóre zapytania kosztem innych zapytań.
Bill Karwin,

10

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

  • nazwa varchar
  • varchar Type
  • decimal NumberValue
  • varchar StringValue
  • date DateValue

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

Potrzebujesz linku do rekordów głównych, które są właścicielami wartości. 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żesz łatwo i szybko filtrować główne rekordy według pól użytkowników.

Możesz chcieć mieć jakieś informacje o metadanych. W rezultacie otrzymujesz:

Tabela UdfMetaData

  • int id
  • nazwa varchar
  • varchar Type

Tabela MasterUdfValues

  • int Master_FK
  • int MetaData_FK
  • decimal NumberValue
  • varchar StringValue
  • date DateValue

Cokolwiek robisz, nie zmieniłbym dynamicznie struktury tabeli. To koszmar konserwacji. Chciałbym również nie używać struktur XML, są zbyt powolne.


Podoba mi się Twoja strategia i może zdecyduję się na nią, ale w 2017 roku zdecydujesz się na coś innego? jak json
maztt

W naszym projekcie zaimplementowaliśmy własne struktury danych, które serializują do czegoś podobnego do json. Posiada interfejs do odczytywania i zapisywania danych bez rzutowania i ze świetną integracją z językiem programowania. To naprawdę świetne. Ma ten sam problem, co wszystkie tego typu „dokumenty” w bazach danych. Trudno jest zapytać o wartości pozorne i nie można łatwo odwoływać się do danych poza „dokumentem”. W zależności od zastosowania oba nie są nawet problemem.
Stefan Steinegger

Poza tym to, co zaproponowałem w 2011 roku, to wciąż aktualne IMHO.
Stefan Steinegger

10

Brzmi to jak problem, który można lepiej rozwiązać za pomocą rozwiązania nierelacyjnego, takiego jak MongoDB lub CouchDB.

Oba pozwalają na dynamiczne rozszerzanie schematu, jednocześnie umożliwiając zachowanie pożądanej integralności krotki.

Zgadzam się z Billem Karwinem, model EAV nie jest dla Ciebie wydajnym 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 pełną krotkę informacji. Gdy jej użycie zmusza cię do dynamicznej rekonstrukcji tabeli w czasie wykonywania, wszystko staje się trudne. Zapytanie staje się ćwiczeniem w zakresie obsługi przestawiania lub wymusza przesunięcie rekonstrukcji krotki w górę do warstwy obiektu.

Nie można określić, czy wartość null lub brakująca wartość jest prawidłowym wpisem lub brakiem wpisu bez osadzenia reguł schematu w warstwie obiektów.

Tracisz możliwość skutecznego zarządzania schematem. Czy 100-znakowy varchar to właściwy typ dla pola „wartość”? 200 znaków? Czy zamiast tego powinien być nvarchar? Może to być trudny kompromis, który kończy się na nałożeniu sztucznych ograniczeń na dynamiczną naturę zestawu. Coś w rodzaju „możesz mieć tylko x pól zdefiniowanych przez użytkownika, a każde z nich może mieć tylko y znaków.

Dzięki rozwiązaniu zorientowanemu na dokumenty, takim jak MongoDB lub CouchDB, zachowujesz wszystkie atrybuty skojarzone z użytkownikiem w ramach jednej krotki. Ponieważ łączenia nie są problemem, życie jest szczęśliwe, ponieważ żadne z nich nie radzi sobie dobrze z połączeniami, pomimo szumu. Twoi użytkownicy mogą zdefiniować tyle atrybutów, ile chcą (lub pozwolisz) na długościach, którymi nie będzie trudno zarządzać, dopóki nie osiągniesz około 4 MB.

Jeśli masz dane, które wymagają integralności na poziomie ACID, możesz rozważyć podzielenie rozwiązania z danymi o wysokiej integralności znajdującymi się w relacyjnej bazie danych, a dynamicznymi danymi w magazynie nierelacyjnym.


6

Nawet jeśli umożliwisz użytkownikowi dodawanie kolumn niestandardowych, niekoniecznie będzie tak, że zapytania dotyczące tych kolumn będą dobrze działać. Projektowanie zapytań ma wiele aspektów, które pozwalają im działać dobrze, z których najważniejszym jest właściwa specyfikacja tego, co powinno być przechowywane w pierwszej kolejności. Czy zatem zasadniczo chcesz pozwolić użytkownikom na tworzenie schematów bez zastanawiania się nad specyfikacjami i móc szybko uzyskiwać informacje z tego schematu? Jeśli tak, jest mało prawdopodobne, że takie rozwiązanie będzie dobrze skalowane, zwłaszcza jeśli chcesz pozwolić użytkownikowi na analizę numeryczną danych.

opcja 1

IMO to podejście daje schemat bez wiedzy o tym, co oznacza schemat, co jest przepisem na katastrofę i koszmarem dla projektantów raportów. Oznacza to, że musisz mieć metadane, aby wiedzieć, która kolumna przechowuje dane. Jeśli te metadane zostaną pomieszane, może to spowodować powiązanie danych. Ponadto ułatwia umieszczenie niewłaściwych danych w niewłaściwej kolumnie. („Co? String1 zawiera nazwę klasztoru? Myślałem, że to ulubiony narkotyk Chalie Sheen”).

Opcja 3,4,5

IMO, wymagania 2, 3 i 4 eliminują wszelkie zmiany EAV. Jeśli potrzebujesz zapytać, posortować lub wykonać obliczenia na tych danych, to EAV jest marzeniem Cthulhu, a koszmarem twojego zespołu programistów i DBA. EAV stworzy wąskie gardło w zakresie wydajności i nie zapewni integralności danych potrzebnej do szybkiego uzyskania żądanych informacji. Zapytania szybko zmienią się w krzyżowe węzły gordyjskie.

Opcja 2,6

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

Jeśli klient chce uzyskać najlepszą wydajność na danych, które chce przechowywać, musi przejść proces współpracy z programistą, aby zrozumieć jego potrzeby, aby były przechowywane tak wydajnie, jak to możliwe. Nadal można go przechowywać w tabeli oddzielonej od pozostałych tabel 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ć ich, aby pomóc konstruktorowi formularzy używać ładnych etykiet, podpowiedzi itp., Aby wszystko, co było konieczne, to dodać schemat. Tak czy inaczej, aby efektywnie tworzyć i uruchamiać raporty, dane muszą być odpowiednio przechowywane. Jeśli dane, o których mowa, będą miały wiele wartości zerowych, niektóre bazy danych mają możliwość przechowywania tego typu informacji. Na przykład,

Gdyby to był tylko zbiór danych, na których nie trzeba było przeprowadzać analizy, filtrowania ani sortowania, powiedziałbym, że pewna odmiana EAV może załatwić sprawę. Jednak biorąc pod uwagę twoje wymagania, najbardziej wydajnym 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.

Rzadkie kolumny


5
  1. Utwórz wiele tabel UDF, po jednej na typ danych. Więc mielibyśmy tabele dla UDFStrings, UDFDates, itp. Prawdopodobnie zrobiłoby to samo co # 2 i automatycznie wygenerowałoby widok za każdym razem, gdy zostanie dodane nowe pole

Według moich badań wiele tabel opartych na typie danych nie pomoże Ci w wydajności. Zwłaszcza jeśli masz zbiorcze dane, takie jak rekordy 20K lub 25K z ponad 50 UDF. Najgorsza była wydajność.

Powinieneś wybrać jedną tabelę z wieloma kolumnami, takimi jak:

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

Powinno to być poprawne i przegłosowane. Poprzednia odpowiedź Phila w 2011 roku nie jest już dobrą radą na dziś 2016.
Yap Kai Lun Leon

Czy mogę uzyskać prosty przykład, jak zrobić taki proces w sql.?
Niroj

Przepraszamy za spóźnioną odpowiedź, ale potrzebujesz struktury bazy danych dla tego samego. Nie dostałem cię @Niroj. Czy możesz szczegółowo wyjaśnić, czego chcesz.
Amit Contractor

4

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

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

EDYTOWAĆ

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

wiele relacyjnych baz danych obsługuje teraz pola oparte na json (które mogą zawierać dynamiczną listę pól podrzędnych) i umożliwia wykonywanie zapytań dotyczących nich

postgress

mysql


1
Nienawidzę pomysłu tworzenia prawdopodobnie setek nieużywanych kolumn. Jest to sprzeczne z tym, czego się nauczyłem i przeczytałem o projektowaniu baz danych SQL. Obecnie mamy ponad 1300 różnych wartości zdefiniowanych przez użytkownika, chociaż wiele z nich to po prostu duplikaty istniejących elementów, które mają inne nazwy.
Rachel

1300 różnych UDF dla jednego stołu? czy każdy użytkownik ma możliwość dodania UDF, czy tylko jakiegoś zaawansowanego użytkownika?
Ophir Yoktan

Jest to część procesu importu ... dodaje wszelkie niezamapowane dane do pola zdefiniowanego przez użytkownika. Ponieważ nikt nie poświęca czasu na mapowanie niezamapowanych danych na istniejące pola UDF, po prostu tworzy nowe i przez lata wiele zostało dodanych.
Rachel,

2

Miałem doświadczenie lub 1, 3 i 4 i wszystkie kończą się niechlujstwem, ponieważ nie jest jasne, jakie są dane, lub są naprawdę skomplikowane z jakąś miękką kategoryzacją, aby podzielić dane na dynamiczne typy rekordów.

Chciałbym wypróbować XML, powinieneś być w stanie wymusić schematy względem zawartości xml, aby sprawdzić typ danych itp., Który pomoże przechowywać różne zestawy danych UDF. W nowszych wersjach serwera SQL można indeksować pola XML, co powinno poprawić wydajność. (patrz http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx ) na przykład


Szczerze mówiąc, w ogóle nie zaglądałem do XMLa. Główną wadą tego jest to, że muszę się nauczyć, jak to działa i jak zapytać o to, a słyszałem, że wydajność może być gorsza niż inne opcje
Rachel,

1
Unikałbym do tego celu XML: może wykonać to zadanie i zaimplementowałem coś takiego w XML w przeszłości, ale wydajność pogorszyła się, gdy struktury danych rosły, a złożoność kodu była wysoka.
Kell,

2

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

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

Opcja 1 brzmi źle i wygląda obrzydliwie, ale pod względem wydajności może być najlepszym rozwiązaniem. Utworzyłem już tabele z kolumnami o nazwie Field00-Field99, ponieważ po prostu nie można pokonać wydajności. Być może będziesz musiał wziąć pod uwagę również wydajność INSERT, w takim przypadku jest to również rozwiązanie, do którego należy się udać. Zawsze możesz utworzyć widoki na tej tabeli, jeśli chcesz, aby wyglądała schludnie!


Dzięki, jeszcze raz przyjrzę się wariantom SQL. Moim największym zmartwieniem jest wydajność i nie jestem pewien, jak sobie z tym poradzę, zwłaszcza jeśli mówimy o ponad 50 milach rzędów
Rachel,

Właśnie dowiedziałem się, że sql_varients nie mogą być używane z klauzulą ​​LIKE ... to dla mnie ogromny minus. Oczywiście, jeśli utworzę widok dla każdego UDF, mógłbym rzucić go na odpowiedni typ danych w oparciu o SQL_VARIANT_PROPERTY (wartość, 'BaseType') ... nadal wydaje się, że jest zły pod względem wydajności
Rachel

Możesz użyć LIKE, ale najpierw musisz rzucić wartość. LIKE działa tylko na varchar, więc musisz rzutować swój sql_variant na varchar. Dopóki wiesz, czy twój UDF jest varchar (np. Ponieważ typ jest przechowywany w innym miejscu), możesz filtrować wszystkie wiersze do varchars, a następnie rzutować i uruchamiać zapytanie LIKE: np. select * FROM MyTable, gdzie variant_type = 'v' Cast (variant_value as varchar (max)) LIKE 'Blah%' W ten sposób nie konwertujesz liczb int i tak dalej na ciągi, które spowalniałyby cię.
Tim Rogers,

Musiałbym uruchomić kilka testów, aby zobaczyć, jaka jest wydajność, zwłaszcza w przypadku milionów wierszy. Czy znasz jakieś artykuły online na temat wydajności przy użyciu sql_varients? Zwłaszcza przy obsadzie i bardzo dużej liczbie płyt?
Rachel,


1

W przeszłości radziłem sobie z tym bardzo skutecznie, używając żadnej z tych opcji (opcja 6? :)).

Tworzę model do zabawy dla użytkowników (przechowuję jako XML i udostępniam za pomocą niestandardowego narzędzia do modelowania) oraz na podstawie tabel i widoków wygenerowanych przez model, aby połączyć tabele podstawowe z tabelami danych zdefiniowanymi przez użytkownika. Zatem każdy typ miałby tabelę podstawową z podstawowymi danymi i tabelę użytkownika z polami zdefiniowanymi przez użytkownika.

Weźmy na przykład dokument: typowe pola to nazwa, typ, data, autor itp. Zostanie to umieszczone w podstawowej tabeli. Następnie użytkownicy definiowaliby własne specjalne typy dokumentów z własnymi polami, takimi jak data_kontraktu, klauzula_nowienia, bla bla bla. W przypadku tego dokumentu zdefiniowanego przez użytkownika istniałaby podstawowa tabela dokumentów, tabela xcontract, połączona na wspólnym kluczu podstawowym (więc klucz podstawowy xcontracts jest również obcy w kluczu podstawowym tabeli podstawowej). Następnie wygenerowałbym widok, aby zawinąć te dwie tabele. Wydajność podczas wykonywania zapytań była szybka. W widokach można również osadzić dodatkowe reguły biznesowe. To zadziałało dla mnie naprawdę dobrze.


1

Nasza baza danych obsługuje aplikację SaaS (oprogramowanie helpdesk), w której użytkownicy mają ponad 7 tys. „Pól niestandardowych”. Stosujemy podejście łączone:

  1. (EntityID, FieldID, Value)stół do wyszukiwania danych
  2. pole JSON w entitiestabeli, które zawiera wszystkie wartości encji, używane do wyświetlania danych. (w ten sposób nie potrzebujesz miliona JOIN, aby uzyskać wartości wartości).

Możesz dalej podzielić # 1, aby uzyskać „tabelę według typu danych”, jak ta odpowiedź sugeruje , w ten sposób możesz nawet indeksować swoje UDF.

PS Kilka słów na obronę podejścia „jednostka-atrybut-wartość”, który wszyscy walczą. Używaliśmy nr 1 bez nr 2 od dziesięcioleci i działał dobrze. Czasami jest to decyzja biznesowa. Czy masz czas na przepisanie aplikacji i przeprojektowanie bazy danych, czy też możesz wrzucić kilka dolarów na serwery w chmurze, które są obecnie naprawdę tanie? Nawiasem mówiąc, kiedy stosowaliśmy podejście nr 1, nasza baza danych zawierała miliony jednostek, do których dostęp miały setki tysięcy użytkowników, a dwurdzeniowy serwer db o 16 GB działał dobrze


Cześć @Alex, natknąłem się na podobny problem. Jeśli dobrze rozumiem, masz: 1) custom_fieldstabelę przechowującą wartości takie jak 1 => last_concert_year, 2 => band, 3 =>, musica następnie custom_fields_valuestabelę z wartościami 001, 1, 1976 002, 1, 1977 003, 2, Iron Maiden003, 3 , Metal Mam nadzieję, że ten przykład ma dla Ciebie sens i przepraszam za formatowanie!
thitami

@thitami nie do końca. Idąc za twoim przykładem: mam bandstabelę z wierszem, 1,'Iron Maiden'a następnie custom_fieldsz wierszami, 1,'concert_year' | 2,'music'a następnie custom_fields_valuesz wierszami1,1,'1977'|1,2,'metal'
Alex

0

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

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

Kiedy masz do czynienia z takim problemem związanym ze strukturą bazy danych, często najlepiej jest wrócić do podstawowego projektu aplikacji (w twoim przypadku systemu importu) i nałożyć na nią kilka dodatkowych ograniczeń.

Teraz zrobiłbym opcję 4 (EDYCJA) 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ę, że tworzysz widoki, aby zoptymalizować wydajność i uzyskać prawidłowe indeksy. Ten poziom normalizacji sprawia, że ​​ślad bazy danych jest mniejszy, ale aplikacja jest bardziej złożona.


0

Polecam # 4, ponieważ tego typu system był używany w Magento, który jest wysoko akredytowaną platformą CMS dla e-commerce. Użyj jednej tabeli, aby zdefiniować własne pola za pomocą kolumn fieldId i label . Następnie należy mieć oddzielne tabele dla każdego typu danych, a w każdej z tych tabel mają indeks, który indeksuje według identyfikatora pola i kolumn wartości typu danych . Następnie w swoich zapytaniach użyj czegoś takiego:

SELECT *
FROM FieldValues_Text
WHERE fieldId IN (
    SELECT fieldId FROM Fields WHERE userId=@userId
)
AND value LIKE '%' + @search + '%'

Moim zdaniem zapewni to najlepszą możliwą wydajność dla typów zdefiniowanych przez użytkownika.

Z mojego doświadczenia wynika, że ​​pracowałem na kilku witrynach Magento, które obsługują miliony użytkowników miesięcznie, obsługują tysiące produktów z niestandardowymi atrybutami produktów, a baza danych z łatwością radzi sobie z obciążeniem, nawet w przypadku raportowania.

Na potrzeby raportowania możesz użyć narzędzia PIVOTdo konwersji wartości etykiet tabeli Pola na nazwy kolumn, a następnie przestawić wyniki zapytania z każdej tabeli typów danych na kolumny przestawne.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.