Czy przechowywanie listy rozdzielanej w kolumnie bazy danych jest tak złe?

Wyobraź sobie formularz internetowy z zestawem pól wyboru (można wybrać dowolne lub wszystkie z nich). Zdecydowałem się zapisać je na oddzielonej przecinkami liście wartości przechowywanych w jednej kolumnie tabeli bazy danych.

Teraz wiem, że poprawnym rozwiązaniem byłoby utworzenie drugiej tabeli i poprawna normalizacja bazy danych. Łatwiej było wdrożyć łatwe rozwiązanie, a ja chciałem szybko przetestować tę aplikację bez konieczności poświęcania jej zbyt wiele czasu.

Myślałem, że zaoszczędzony czas i prostszy kod były tego warte w mojej sytuacji, czy to uzasadniony wybór projektu, czy też powinienem był go znormalizować od samego początku?

Więcej kontekstu, jest to mała wewnętrzna aplikacja, która zasadniczo zastępuje plik Excela, który był przechowywany w folderze współdzielonym. Pytam również, ponieważ myślę o oczyszczeniu programu i uczynieniu go łatwiejszym w utrzymaniu. Jest kilka rzeczy, z których nie jestem do końca zadowolony, jedna z nich jest tematem tego pytania.

Oprócz naruszenia pierwszej normalnej formy z powodu powtarzającej się grupy wartości przechowywanych w jednej kolumnie, listy oddzielone przecinkami mają wiele innych praktycznych problemów:

• Nie mogę się upewnić, że każda wartość ma odpowiedni typ danych: nie ma sposobu, aby zapobiec 1,2,3, banan, 5
• Nie można używać ograniczeń klucza obcego do łączenia wartości z tabelą odnośników; nie ma sposobu na wymuszenie integralności referencyjnej.
• Nie można wymusić wyjątkowości: nie ma sposobu, aby zapobiec 1,2,3,3,3,5
• Nie można usunąć wartości z listy bez pobrania całej listy.
• Nie można przechowywać listy dłużej niż mieści się w kolumnie ciągu.
• Trudno wyszukać wszystkie podmioty o podanej wartości na liście; musisz użyć nieefektywnego skanowania tabeli. Może być konieczne użycie wyrażeń regularnych, na przykład w MySQL:
  idlist REGEXP '[[:<:]]2[[:>:]]'^*
• Trudno policzyć elementy na liście lub wykonać inne zapytania agregujące.
• Trudno połączyć wartości z tabelą odnośników, do której się odnoszą.
• Trudno pobrać listę w posortowanej kolejności.

Aby rozwiązać te problemy, musisz napisać mnóstwo kodu aplikacji, wymyślając na nowo funkcjonalność, którą RDBMS zapewnia już znacznie wydajniej .

Listy rozdzielone przecinkami są na tyle niepoprawne, że uczyniłem to pierwszym rozdziałem mojej książki: SQL Antipatterns: Unikanie pułapek programowania baz danych .

Są chwile, kiedy trzeba zastosować denormalizację, ale jak wspomina @OMG Ponies , są to wyjątki. Wszelkie nierelacyjne „optymalizacje” przynoszą korzyści dla jednego rodzaju zapytań kosztem innych zastosowań danych, więc upewnij się, że wiesz, które z twoich zapytań należy traktować tak, aby zasługiwały na denormalizację.

^* MySQL 8.0 nie obsługuje już tej składni wyrażenia granicznego słowa.

„Jednym z powodów było lenistwo”.

To dzwoni dzwonkami alarmowymi. Jedynym powodem, dla którego powinieneś zrobić coś takiego, jest to, że wiesz, jak to zrobić „we właściwy sposób”, ale doszedłeś do wniosku, że istnieje namacalny powód, aby tego nie robić.

Powiedziawszy to: jeśli dane, które chcesz przechowywać w ten sposób, są danymi, o które nigdy nie będziesz musiał pytać, może być uzasadnione przechowywanie ich w sposób, który wybrałeś.

(Niektórzy użytkownicy zakwestionowaliby stwierdzenie z mojego poprzedniego akapitu, mówiąc, że „nigdy nie wiesz, jakie wymagania zostaną dodane w przyszłości”. Użytkownicy ci są wprowadzani w błąd lub wyrażają przekonanie religijne. Czasami korzystne jest zastosowanie wymagań mieć przed sobą.)

Istnieje wiele pytań dotyczących SO:

• jak uzyskać liczbę określonych wartości z listy rozdzielanej przecinkami
• jak uzyskać rekordy, które mają tylko tę samą określoną wartość 2/3 / etc z listy oddzielonej przecinkami

Innym problemem związanym z listą rozdzielaną przecinkami jest zapewnienie spójności wartości - przechowywanie tekstu oznacza możliwość literówek ...

Są to wszystkie objawy danych zdenormalizowanych i podkreślają, dlaczego zawsze należy modelować dane znormalizowane. Denormalizacja może być optymalizacją zapytania, którą można zastosować, gdy zajdzie taka potrzeba .

Zasadniczo wszystko można obronić, jeśli spełnia wymagania twojego projektu. To nie znaczy, że ludzie się zgodzą lub będą chcieli bronić twojej decyzji ...

Ogólnie rzecz biorąc, przechowywanie danych w ten sposób jest nieoptymalne (np. Trudniejsze do wykonania wydajne zapytania) i może powodować problemy z konserwacją, jeśli zmodyfikujesz elementy w formularzu. Być może mógłbyś znaleźć środek i użyć liczby całkowitej reprezentującej zestaw flag bitowych?

Tak, powiedziałbym, że to naprawdę takie złe. Jest to wybór, który można obronić, ale to nie czyni go poprawnym ani dobrym.

Łamie pierwszą normalną formę.

Druga krytyka polega na tym, że umieszczenie surowych danych wejściowych bezpośrednio w bazie danych, bez jakiejkolwiek weryfikacji lub wiązania, pozostawia cię otwartym na ataki typu SQL injection.

To, co nazywacie lenistwem i brakiem znajomości SQL, to rzeczy, z których zbudowani są neofici. Polecam poświęcić czas, aby zrobić to poprawnie i postrzegać to jako okazję do nauki.

Lub pozostaw to bez zmian i naucz się bolesnej lekcji ataku typu SQL injection.

Cóż, korzystam z listy rozdzielanych parami klucz / wartość w kolumnie NTEXT w SQL Server od ponad 4 lat i to działa. Tracisz elastyczność tworzenia zapytań, ale z drugiej strony, jeśli masz bibliotekę, która utrzymuje / wykracza poza parę kluczowych wartości, nie jest to zły pomysł.

Potrzebowałem kolumny z wieloma wartościami, można ją zaimplementować jako pole xml

W razie potrzeby można go przekształcić w przecinek

odpytanie listy XML na serwerze SQL za pomocą Xquery .

Będąc polem XML, niektóre z problemów można rozwiązać.

Z CSV: Nie mogę upewnić się, że każda wartość ma odpowiedni typ danych: nie ma sposobu, aby zapobiec 1,2,3, banan, 5

Dzięki XML: wartości w znaczniku mogą być zmuszone do poprawnego typu

Z CSV: nie można używać ograniczeń klucza obcego do łączenia wartości z tabelą odnośników; nie ma sposobu na wymuszenie integralności referencyjnej.

Z XML: wciąż problem

Dzięki CSV: nie można egzekwować wyjątkowości: nie można zapobiec 1,2,3,3,3,5

Z XML: wciąż problem

Z CSV: nie można usunąć wartości z listy bez pobrania całej listy.

Dzięki XML: pojedyncze elementy można usunąć

Z CSV: Trudno wyszukać wszystkie podmioty o podanej wartości na liście; musisz użyć nieefektywnego skanowania tabeli.

Dzięki XML: pole xml może być indeksowane

Z CSV: Trudno policzyć elementy na liście lub wykonać inne agregowane zapytania. **

Z XML: niezbyt trudne

Z CSV: Trudno połączyć wartości z tabelą odnośników, do której się odnoszą. **

Z XML: niezbyt trudne

Z CSV: Trudno pobrać listę w posortowanej kolejności.

Z XML: niezbyt trudne

Z CSV: Przechowywanie liczb całkowitych jako ciągów zajmuje około dwa razy więcej miejsca niż przechowywanie liczb całkowitych binarnych.

Z XML: pamięć jest nawet gorsza niż csv

Z CSV: Plus wiele znaków przecinków.

W przypadku XML: zamiast przecinków używane są tagi

Krótko mówiąc, użycie XML pozwala obejść niektóre problemy z listą rozdzielaną ORAZ można ją w razie potrzeby przekonwertować na listę rozdzielaną

Tak, to jest aż tak źle. Moim zdaniem, jeśli nie lubisz korzystać z relacyjnych baz danych, poszukaj alternatywy, która bardziej Ci odpowiada, istnieje wiele interesujących projektów „NOSQL” z kilkoma naprawdę zaawansowanymi funkcjami.

Prawdopodobnie wziąłbym środek: uczynię każde pole w CSV oddzielną kolumną w bazie danych, ale nie martwię się zbytnio o normalizację (przynajmniej na razie). W pewnym momencie normalizacja może stać się interesująca, ale przy wszystkich danych zapisanych w jednej kolumnie praktycznie nie zyskujesz na korzystaniu z bazy danych. Musisz podzielić dane na pola logiczne / kolumny / cokolwiek chcesz je nazwać, zanim będziesz mógł w ogóle manipulować nimi w sposób znaczący.

Jeśli masz stałą liczbę pól boolowskich, możesz użyć INT(1) NOT NULL(lub BIT NOT NULLjeśli istnieje) lub CHAR (0)(zerowalne) dla każdego z nich. Możesz także użyć SET(nie pamiętam dokładnej składni).