Możliwe korzyści przechowywania wielu wartości w jednym polu jednego wiersza zamiast w osobnych wierszach

Podczas naszego ostatniego cotygodniowego spotkania osoba, która nie ma doświadczenia w administrowaniu bazami danych, poruszyła następujące pytanie:

„Czy byłby scenariusz uzasadniający przechowywanie danych w wierszu (ciąg) zamiast kilku wierszy?”

Załóżmy tabelę o nazwie, w countryStatesktórej chcemy przechowywać stany kraju; Wykorzystam w tym przykładzie USA i nie będę wymieniał wszystkich stanów ze względu na lenistwo.

Tam mielibyśmy dwie kolumny; jeden dzwonił, Countrya drugi dzwonił States. Jak omówiono tutaj i zaproponowano w odpowiedzi @ srutzky , PKbędzie to kod zdefiniowany w ISO 3166-1 alfa-3 .

Nasz stół wyglądałby tak:

+---------+-----------------------+-------------------------------------------------------+
| Country | States                | StateName                                             |
+---------+-----------------------+-------------------------------------------------------+
| USA     | AL, CA, FL,OH, NY, WY | Alabama, California, Florida, Ohio, New York, Wyoming |
+---------+-----------------------+-------------------------------------------------------+

Zadając to samo pytanie znajomemu programistom, powiedział, że z punktu widzenia wielkości ruchu danych może to być przydatne, ale nie, jeśli będziemy musieli manipulować tymi danymi. W takim przypadku musiałaby istnieć inteligencja w kodzie aplikacji, która mogłaby przekształcić ten ciąg w listę (powiedzmy, że oprogramowanie, które ma dostęp do tej tabeli, musi utworzyć pole kombi).

Doszliśmy do wniosku, że ten model nie jest zbyt przydatny, ale zacząłem podejrzewać, że może istnieć sposób, aby uczynić go użytecznym.

Chciałbym zapytać, czy któryś z was już widział, słyszał lub robił coś takiego w sposób, który naprawdę działa .

Na początek obecny tytuł pytania odnoszący się do „przechowywania danych jako ciągu zamiast kolumn” jest nieco mylący. Mówiąc o przechowywaniu danych jako ciągów zamiast czegoś innego, zwykle odnosi się to do szeregowania wszystkiego do formatu ciągu zamiast właściwego / silnego typu danych (np. INTLub DATETIME). Ale pytanie o przechowywanie danych jako wielu wartości w jednym polu zamiast w osobnych wierszach jest nieco inne. I szczerze mówiąc, podczas gdy łączenie wartości najłatwiej jest wykonać za pomocą łańcuchów, można to również zrobić za pomocą INTi BINARYtypów, albo przez maskowanie bitów lub podobnie rezerwowanie pewnych pozycji, aby mieć różne znaczenia. Ponieważ pytamy o drugą interpretację, opierając się na tekście pytania, zajmiemy się tym.

Jednym słowem: Nie. Jeśli przechowujesz rzeczywiste punkty danych, spowoduje to tylko ból (pod względem kodu i wydajności), ponieważ jest to niepotrzebną komplikacją. Jeśli jest to wartość, która będzie zawsze przechowywana jako pojedyncza jednostka, aktualizowana jako pojedyncza jednostka i nigdy nie demontowana w bazie danych, może to być w porządku, ponieważ jest mniej więcej analogiczne do przechowywania obrazu lub pliku PDF. W przeciwnym razie każda próba parsowania danych spowoduje unieważnienie przy użyciu jakichkolwiek indeksów (np. Przy użyciu LIKE '%something%'lub CHARINDEX, lub PATINDEX, lub SUBSTRINGitd.).

Jeśli musisz przechowywać osobne wartości w jednym polu w jednym wierszu, istnieją bardziej odpowiednie sposoby: XML lub JSON. Są to możliwe do przeanalizowania formaty ( XML / JSON ), a XML można nawet indeksować . Ale idealnie te dane byłyby przechowywane w odpowiednio wpisanych polach, aby mogły być naprawdę przydatne.

I proszę nie zapominać, że celem RDBMS jest przechowywanie danych w taki sposób, aby można je było pobierać i przetwarzać tak skutecznie, jak to możliwe, w ramach ograniczeń nałożonych przez zgodność z ACID . Pobieranie skonkatenowanych wartości jest wystarczająco złe ze względu na potrzebę parsowania wartości w pierwszej kolejności i nie jest to możliwe do zindeksowania. Ale manipulowanie często oznacza zastąpienie całego obiektu blob tylko w celu zaktualizowania jego części (przy założeniu, że nie istnieje wzorzec do użycia z REPLACEfunkcją). Typ danych XML pozwala przynajmniej na XML DML dla uproszczonych aktualizacji, choć wciąż nie są one tak szybkie jak zwykła aktualizacja odpowiednio modelowanych danych.

Ponadto, biorąc pod uwagę scenariusz taki jak pokazany w powyższym pytaniu, łącząc wszystkie kody stanu razem, nie będziesz w stanie uzyskać klucza obcego (w żadnym kierunku) tych wartości.

A co, jeśli wymagania biznesowe zmieniają się w czasie i musisz śledzić dodatkowe właściwości tych przedmiotów? Jeśli chodzi o „stany”, a co ze stolicami, populacją, porządkiem sortowania lub czymkolwiek innym? Przechowywane poprawnie jako wiersze, możesz dodać więcej kolumn dla dodatkowych właściwości. Jasne, możesz mieć wiele poziomów analizowalnych danych, na przykład, |StateCode,Capital,Population |StateCode,Capital,Populate|...ale mam nadzieję, że każdy zauważy, że problem wykładniczo wymyka się spod kontroli. Oczywiście, ten konkretny problem dość łatwo można rozwiązać w formatach XML i JSON, i to jest ich wartość, jak wspomniano powyżej. Ale nadal potrzebujesz bardzo dobrego powodu, aby użyć któregokolwiek z nich jako początkowego sposobu modelowania, ponieważ żadne z nich nigdy nie będzie tak wydajne, jak użycie dyskretnych pól w oddzielnych wierszach.

Użyłem czegoś takiego do bardzo ograniczonego celu. Stworzyliśmy tabelę nagłówków dla plików wyjściowych. Były one specjalnie skonstruowane i były głównie nagłówkami kolumn, ale nie do końca. Dane wyglądały więc podobnie

OutputType   OutputHeader
PersonalData Name|Address|City|State|Zip
JobInfo      Name|JobName|JobTitle

Zasadniczo wyglądało na to, że jest to lista rozdzielana ograniczeniami. I w pewnym sensie tak było. Ale dla naszych celów był to jeden długi ciąg.

To jest sztuczka tutaj. Jeśli nigdy nie planujesz analizować listy, warto ją zapisać. Jeśli jednak będziesz musiał lub nawet będziesz musiał przeanalizować listę, warto poświęcić więcej miejsca i czasu, aby ją rozdzielić i zapisać w osobnych wierszach.

Użyłem go raz z raczej małym stołem, na przykład:

CREATE TABLE t1 (
  ID number,
  some_feature   varchar2(100),
  valid_channels  varchar2(100));

CREATE TABLE channel_def (
  channel varchar2(100));

A następnie zapisz wartości CRM,SMS,SELF-CAREw valid_channel.

Cały stół ma około 10 rekordów. valid_channelzawiera wartości, które powinny znajdować się w tabeli łączącej, która przedstawia relację wiele do wielu. Stół t1nie będzie intensywnie używany, więc postanowiliśmy pójść tą drogą. W tę decyzję zaangażowana była jednak pewna polityka (patrz poniżej).

Ale generalnie unikam tego, to nie jest 3NF.

W miejscu, w którym pracuję, jest obecnie mnóstwo takich kolumn. Ich uzasadnieniem jest to, że ułatwia to ich zapytania: zamiast łączyć trzy tabele za pomocą tabeli łączącej, mogą przejść bezpośrednio do tabeli definicji za pomocą LIKE. Na przykład

SELECT * 
  FROM t1 
 INNER JOIN channel_def cd
    ON ','||t1.valid_channels||',' LIKE '%,'||cd.channel||',%';

Horrible + na Oracle wyłącza korzystanie z indeksu z powodu uruchamiania '%,'.

Dokonano tego tutaj na SE. Jak pisze Marc Gravell :

... Po namyśle i przemyśleniu zdecydowaliśmy się na naturalną reprezentację oddzieloną rurą (prętem) z rurami prowadzącymi / końcowymi, więc „.net c #” staje się po prostu „| .net | c # |”. Ma to zalety:

• bardzo prosty do analizy
• zbiorczą aktualizację i usuwanie tagów można wykonać za pomocą prostej zamiany (w tym potoków, aby uniknąć zastąpienia dopasowań środkowych tagów)
• ...

Ten „nowy format” był kolejnym krokiem od „starego formatu”, który był nieco inny i został wybrany do korzystania z funkcji wyszukiwania pełnotekstowego programu SQL Server, więc niektóre korzyści nie są istotne, jeśli robisz to od zera.

Prawdopodobnie nie w pełni znormalizowali to ze względu zarówno na ilość pracy, jak i wydajność.

Cóż, jedną z głównych zalet używania ciągów i innych typów danych jest wysyłanie ich z SQL Server do C #, C, C ++ (itp.) Za pomocą SQLCLR, gdy może być potrzebna sama wydajność. Możesz nawet utworzyć widok lub procedurę przechowywaną do reprezentowania danych relacyjnych nierelacyjnie - tak jak w powyższym przykładzie do tego właśnie celu.

Zobacz ten przykład:

http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/

według Wikipedii: SQL CLR lub SQLCLR (SQL Common Language Runtime) to technologia do obsługi silnika uruchomieniowego Microsoft .NET w języku wspólnym w SQL Server. SQLCLR umożliwia zarządzanie kodem zarządzanym przez środowisko Microsoft SQL Server i uruchamianie go w tym środowisku.

Moim zdaniem odpowiedź brzmi „nie”. Nie zastosowałem tego podejścia i uniknęłbym go - nie mogę wymyślić powodu, dla którego wybrałem tę trasę. Opierasz się na świecie JSON / NoSQL z tablicą.

W poprzedniej roli mieliśmy podobne wybory projektowe, w których zespół architektów chciał mieć pole „Dane”, które zostało rozdzielone, a następnie przekształcone na binarne. Ostatecznie nie poszliśmy tą drogą z kilku powodów.

Gdybyś musiał dołączyć do tego typu danych, byłoby to jedno brzydkie doświadczenie. Aktualizacja pojedynczych elementów ciągu również byłaby nieprzyjemna.