Korzystanie z jednorzędowej tabeli konfiguracji w bazie danych SQL Server. Kiepski pomysł?


145

Tworząc aplikację koszyka na zakupy stwierdziłem, że muszę zapisać ustawienia i konfiguracje w oparciu o preferencje i wymagania administratora. Mogą to być informacje o firmie, identyfikatory kont wysyłkowych, klucze API PayPal, preferencje powiadomień itp.

Tworzenie tabeli do przechowywania pojedynczego wiersza w systemie relacyjnej bazy danych wydaje się wysoce niewłaściwe.

Jaki jest właściwy sposób przechowywania tych informacji?

Uwaga: mój DBMS to SQL Server 2008, a warstwa programowania jest zaimplementowana w ASP.NET (w C #).

Odpowiedzi:


189

W przeszłości robiłem to na dwa sposoby - tabela z jednym wierszem i tabela par klucz / wartość - i każde podejście ma zalety i wady.

Jeden rząd

  • dodatni: wartości są przechowywane we właściwym typie
  • pozytywny: łatwiej poradzić sobie w kodzie (dzięki temu)
  • pozytywne: wartości domyślne można nadać każdemu ustawieniu indywidualnie
  • negatywne: aby dodać nowe ustawienie, wymagana jest zmiana schematu
  • minus: stół może stać się bardzo szeroki, jeśli jest dużo ustawień

Para klucz / wartość

  • pozytywne: dodanie nowych ustawień nie wymaga zmiany schematu
  • pozytywne: schemat tabeli jest wąski, a dodatkowe wiersze są używane do nowych ustawień
  • negatywne: każde ustawienie ma tę samą wartość domyślną (null / puste?)
  • negatywne: wszystko musi być przechowywane jako ciągi (np. nvarchar)
  • negatywne: kiedy masz do czynienia z ustawieniami w kodzie, musisz wiedzieć, jakiego typu jest ustawienie i rzucić je

Opcja pojedynczego rzędu jest zdecydowanie najłatwiejsza w użyciu. Dzieje się tak, ponieważ możesz przechowywać każde ustawienie w odpowiednim typie w bazie danych i nie musisz przechowywać typów ustawień, a także ich kluczy wyszukiwania w kodzie.

Jedną rzeczą, którą martwiłem się przy użyciu tego podejścia, było posiadanie wielu wierszy w „specjalnej” tabeli ustawień pojedynczego wiersza. Pokonałem to przez (w SQL Server):

  • dodanie nowej kolumny bitowej z domyślną wartością 0
  • tworzenie ograniczenia sprawdzającego, aby upewnić się, że ta kolumna ma wartość 0
  • tworząc unikalne ograniczenie kolumny bitowej

Oznacza to, że w tabeli może istnieć tylko jeden wiersz, ponieważ kolumna bitowa musi mieć wartość 0, ale może istnieć tylko jeden wiersz z tą wartością ze względu na unikalne ograniczenie.


5
W naszej aplikacji LOB robimy rzecz jednorzędową. Wszystkie wartości są poprawnego typu, co znacznie upraszcza ich użycie w aplikacji. Nasz schemat jest wersjonowany wraz z aplikacją, więc zmiana konfiguracji jest zarządzana tak, jak każda wersja aplikacji.
DaveE,

17
Dodatni pojedynczy wiersz: można zdefiniować FK w niektórych kolumnach!
wqw

8
Zawsze możesz utworzyć parę klucz / wartość z identyfikatorem typu, aby określić, która kolumna ma wartość w swoim typie wartości. Daje to to, co najlepsze z obu światów i możesz użyć przechowywanego procesu, aby uzyskać wartość, gdy jej potrzebujesz.
Middletone

19
Jedną rzeczą, która może naprawdę zrujnować twój dzień po wdrożeniu rozwiązania jednorzędowego, jest to, że później otrzymujesz zadanie „
śledźmy

6
Kolejna zaleta rozwiązania jednorzędowego, którą odkryłem w jednym przypadku: miałem aplikację zbudowaną dla jednego klienta, z jednorzędową tabelą dla „ustawień”. Później dostałem dwóch innych klientów, którzy chcieli używać tej samej aplikacji, ale chcieli innych ustawień: wszystko, co musiałem zrobić, to dodać PK „client_id” do tabeli, aby zachować oddzielny zestaw ustawień dla każdego klienta. (To wtedy zdajesz sobie sprawę, że te „ustawienia” to tak naprawdę tylko atrybuty dla encji wyższego poziomu, której jeszcze nie wymodelowałeś.)
Jeffrey Kemp

10

Powinieneś utworzyć tabelę z kolumną dla typu informacji i wartości informacji (przynajmniej). W ten sposób unikniesz konieczności tworzenia nowych kolumn za każdym razem, gdy dodawane są nowe informacje.


1
Proste i schludne. Po prostu pracuj z listą par klucz-wartość z tego miejsca. Możesz trochę pomyśleć o wartościach domyślnych, zależy to od kontekstu użycia ...
Paul Kohler,

4
Dlaczego tworzenie nowych kolumn jest problemem? Wiem, że są sytuacje, w których programiści muszą tego unikać z powodu problemów politycznych związanych z aktualizacją schematów SQL, ale nie ma o tym wzmianki w pytaniu.
finnw

6

Pojedynczy rząd będzie działał dobrze; będzie miał nawet silne typy:

show_borders    bit
admin_name      varchar(50)
max_users       int

Jedną z wad jest to, że wymaga zmiany schematu ( alter table), aby dodać nowe ustawienie. Jedną z alternatyw jest normalizacja, w wyniku której otrzymujesz tabelę taką jak:

pref_name       varchar(50) primary key
pref_value      varchar(50) 

Ma słabe typy (wszystko jest varchar), ale dodanie nowego ustawienia jest po prostu dodaniem wiersza, co można zrobić tylko z dostępem do zapisu w bazie danych.


4

Osobiście zapisałbym go w jednym rzędzie, jeśli to działa. Przesadne przechowywanie go w tabeli SQL? prawdopodobnie, ale nie ma w tym żadnej szkody.


4

Jak się domyślacie, z wyjątkiem najprostszych sytuacji, umieszczenie wszystkich parametrów konfiguracji w jednym wierszu ma wiele wad. To zły pomysł ...

Wygodnym sposobem przechowywania informacji o konfiguracji i / lub preferencjach użytkownika jest format XML . Wiele DBMS obsługuje typ danych XML. Składnia XML umożliwia wykorzystanie „języka” i struktury opisującej konfigurację w miarę jej rozwoju. Jedną z zalet XML jest niejawna obsługa struktury hierarchicznej, co pozwala na przykład na przechowywanie małych list parametrów konfiguracyjnych bez konieczności nadawania im nazwy przyrostka z numerem. Możliwą wadą formatu XML jest to, że wyszukiwanie i ogólnie modyfikowanie tych danych nie jest tak proste, jak inne podejścia (nic skomplikowanego, ale nie tak proste / naturalne)

Jeśli chcesz pozostać bliżej modelu relacyjnego , prawdopodobnie potrzebujesz modelu Entity-Attribute-Value , w którym poszczególne wartości są przechowywane w tabeli, która zwykle wygląda następująco:

EntityId     (foreign key to the "owner" of this attribute)
AttributeId  (foreign key to the "metadata" table where the attribute is defined)
StringValue  (it is often convenient to have different columns of different types
IntValue      allowing to store the various attributes in a format that befits 
              them)

Gdzie AttributeId jest kluczem obcym do tabeli, w której zdefiniowany jest każdy możliwy atrybut ("parametr konfiguracyjny" w twoim przypadku), powiedzmy

AttributeId  (Primary Key)
Name
AttributeType     (some code  S = string, I = Int etc.)
Required          (some boolean indicating that this is required)
Some_other_fields   (for example to define in which order these attributes get displayed etc...)

Wreszcie EntityId pozwala zidentyfikować jakąś jednostkę, która „posiada” te różne atrybuty. W twoim przypadku może to być identyfikator użytkownika lub nawet niejawny, jeśli masz tylko jedną konfigurację do zarządzania.

Oprócz umożliwienia powiększania listy możliwych parametrów konfiguracyjnych wraz z rozwojem aplikacji, model EAV umieszcza „metadane”, tj. Dane odnoszące się do samego Atrybutu, w plikach danych, unikając w ten sposób całego kodowania nazw kolumn, które są powszechnie spotykane gdy parametry konfiguracyjne są przechowywane w jednym wierszu.


3
W przypadku większości zastosowań tabeli konfiguracji brzmi to jak przesada.
JerryOL

Myślę, że ogólna idea tego podejścia jest świetna. Ale dlaczego XML? Po prostu wybierz prosty format wymiany danych, taki jak JSON lub YAML, a uzyskasz zalety obu innych odmian.
schlamar

1
EAV jest relacyjny, ale nie jest znormalizowany. Z pewnością istnieją dla niego przypadki użycia (na przykład systemy ORM wydają się je kochać), ale argument, że metadane znajdują się w bazie danych dla EAV, nie jest przekonującym powodem, aby z nich korzystać. Wszystkie RDBMS zawierają metadane w tabelach systemowych, które można wykryć, więc tabele jednowierszowe zawierają również metadane w bazie danych. Zakodowane na stałe nazwy kolumn również nie stanowią problemu. Jeśli używasz kluczy dla encji i atrybutów, masz zakodowaną na stałe tabelę wyszukiwania w innym miejscu, która je definiuje (lub, co gorsza, jest w warstwie prezentacji).
Davos

3

Z pewnością nie musisz zmieniać schematu podczas dodawania nowego parametru konfiguracji w znormalizowanym podejściu, ale prawdopodobnie nadal zmieniasz kod, aby przetworzyć nową wartość.

Dodanie „tabeli zmian” do wdrożenia nie wydaje się być tak dużym kompromisem dla prostoty i bezpieczeństwa typu podejścia opartego na jednym wierszu.


2

Para klucz i wartość jest podobna do .Net App.Config, która może przechowywać ustawienia konfiguracji.

Więc jeśli chcesz pobrać wartość, którą możesz zrobić:

SELECT value FROM configurationTable
WHERE ApplicationGroup = 'myappgroup'
AND keyDescription = 'myKey';

1

Typowym sposobem na to jest posiadanie tabeli „właściwości”, która jest identyczna z plikiem właściwości. Tutaj możesz przechowywać wszystkie stałe aplikacji lub nie tak stałe rzeczy, które po prostu musisz mieć pod ręką.

Następnie możesz pobrać informacje z tej tabeli, gdy ich potrzebujesz. Podobnie, gdy stwierdzisz, że masz inne ustawienia do zapisania, możesz je dodać. Oto przykład:

property_entry_table

[id, scope, refId, propertyName, propertyValue, propertyType] 
1, 0, 1, "COMPANY_INFO", "Acme Tools", "ADMIN"  
2, 0, 1, "SHIPPING_ID", "12333484", "ADMIN"  
3, 0, 1, "PAYPAL_KEY", "2143123412341", "ADMIN"   
4, 0, 1, "PAYPAL_KEY", "123412341234123", "ADMIN"  
5, 0, 1, "NOTIF_PREF", "ON", "ADMIN"  
6, 0, 2, "NOTIF_PREF", "OFF", "ADMIN"   

W ten sposób możesz przechowywać dane, które posiadasz oraz dane, które będziesz mieć w przyszłym roku, o których jeszcze nie wiesz :).

W tym przykładzie twój zakres i refId mogą być używane do wszystkiego, co chcesz na zapleczu. Więc jeśli propertyType „ADMIN” ma zakres 0 refId 2, wiesz, jakie to preferencje.

Typ nieruchomości przydaje się, gdy pewnego dnia będziesz musiał przechowywać tutaj również informacje niebędące administratorami.

Pamiętaj, że nie powinieneś przechowywać danych koszyka w ten sposób ani wyszukiwać w tej sprawie. Jeśli jednak dane są specyficzne dla systemu , z pewnością możesz użyć tej metody.

Na przykład: jeśli chcesz przechowywać swoją DATABASE_VERSION , użyłbyś takiej tabeli. W ten sposób, gdy musisz zaktualizować aplikację, możesz sprawdzić tabelę właściwości, aby zobaczyć, jaką wersję oprogramowania ma klient.

Chodzi o to, że nie chcesz tego używać do rzeczy, które dotyczą koszyka. Utrzymuj logikę biznesową w dobrze zdefiniowanych tabelach relacyjnych. Tabela właściwości służy tylko do informacji systemowych.


@finnw Całkowicie zgadzam się, że ta metoda nie powinna być używana do wyszukiwania, zwłaszcza gdy istnieje wiele różnych typów wyszukiwań. Być może źle zrozumiałem pytanie. Wyglądało na to, że potrzebował tabeli dla stałych i właściwości systemu. W takim razie po co mieć 10 różnych tabel?
Stephano

uwaga: powiedział „zapisz ustawienia i konfiguracje”, a nie „muszę zapisać dane koszyka relacyjnego”
Stephano,

Moim zarzutem jest to, że pomijasz pisanie SQL i inne mechanizmy ograniczające, aby uniknąć aktualizowania schematu SQL podczas dodawania nowych atrybutów. Jak mówisz „dane, które będziesz mieć w przyszłym roku, o których jeszcze nie wiesz”. Tak, w przyszłym roku będziesz mieć nowe dane, ale co powstrzyma Cię przed tworzeniem nowych (wpisywanych) kolumn SQL, sprawdzaniem i prawdopodobnie ograniczeniami klucza obcego w momencie dodawania?
finnw

Moim pierwszym odruchem jest po prostu dodanie tych danych do płaskiego pliku. I masz rację, ten proces używania tabeli zamiast tego rzeczywiście pozwoli obejść mechanizmy ograniczeń DBMS. Jednak powiedziałbym, że jeśli zbytnio się starasz przestrzegać odpowiednich technik bazodanowych, tracisz sens. Sprawdź pierwszą odpowiedź; najwyższy głos na SO: stackoverflow.com/questions/406760/…
Stephano,

2
Poszedłbym w parę klucz-wartość, wrzuciłbym to wszystko do słownika po uruchomieniu i posortowaniu.
Paul Creasey,

0

Nie jestem pewien, czy pojedynczy wiersz jest najlepszą implementacją do konfiguracji. Lepiej byłoby mieć wiersz na element konfiguracji z dwiema kolumnami (configName, configValue), chociaż będzie to wymagało rzutowania wszystkich wartości na ciągi i z powrotem.

Niezależnie od tego nie ma nic złego w używaniu jednego wiersza do konfiguracji globalnej. Inne opcje przechowywania go w bazie danych (zmienne globalne) są gorsze. Możesz to kontrolować, wstawiając pierwszy wiersz konfiguracji, a następnie wyłączając wstawianie w tabeli, aby zapobiec tworzeniu wielu wierszy.


0

Możesz wykonać parę klucz / wartość bez konwersji, dodając kolumnę dla każdego głównego typu i jedną kolumnę informującą, w której kolumnie znajdują się dane.

Twój stół wyglądałby więc mniej więcej tak:

id, column_num, property_name, intValue, floatValue, charValue, dateValue
1, 1, weeks, 51, , ,
2, 2, pi, , 3.14159, , 
3, 4, FiscYearEnd, , , , 1/31/2015
4, 3, CompanyName, , , ACME, 

Zajmuje trochę więcej miejsca, ale co najwyżej używasz kilkudziesięciu atrybutów. Możesz użyć instrukcji case poza wartością column_num, aby wyciągnąć / dołączyć do odpowiedniego pola.


0

Przepraszam, że przyszedłem, tak później. Ale w każdym razie to, co robię, jest proste i skuteczne. Po prostu tworzę tabelę z trzema () kolumnami:

ID - int (11)

nazwa - varchar (64)

wartość - tekst

To, co robię przed utworzeniem nowej kolumny konfiguracyjnej, aktualizacją lub odczytem, ​​to serializowanie „wartości”! W ten sposób jestem pewien typu (cóż, php jest :))

Na przykład:

b: 0; jest dla B OOLEAN ( fałsz )

b: 1; jest dla B OOLEAN ( prawda )

i: 1988; jest dla I NT

s: 5: "Kader"; dotyczy S TRINGU o długości 5 znaków

Mam nadzieję, że to pomoże :)


1
Dlaczego po prostu nie utworzyć nowej kolumny dla tego typu? i:1988wygląda na to, że próbujesz zwinąć dwie informacje w jedną kolumnę.
maksymiuk

@maksymiuk Po prostu, ponieważ po odserializowaniu otrzymujesz dokładny typ zamiast używania pętli po (if lub switch) ... itd.
Kader Bouyakoub

nie potrzeba żadnych pętli, przełączników ani niczego innego, faktycznie wyeliminowałoby to konieczność parsowania informacji z każdego wiersza, podczas gdy gdybyś miał dodatkową kolumnę dla typu, informacje o typie są już dostępne dla komponentu pobierającego informacje bez konieczności wykonywania żadnych dalszych kroków niż tylko zapytanie wstępne
maksymiuk

Masz na myśli robienie czegoś takiego jak echo (int) $vardla liczby całkowitej i innych dla innych typów?
Kader Bouyakoub

0

Miej kolumnę kluczową jako varchar i kolumnę wartości jako JSON. 1jest numeryczne, podczas gdy "1"jest łańcuchem. truei falseoba są logiczne. Możesz też mieć przedmioty.

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.