Najlepszy sposób na przechowywanie jednostek w bazie danych

Odziedziczyłem dużą bazę danych (SQLServer) z setkami kolumn reprezentujących ilości jednej rzeczy. Jednostki tych wartości (np. „Galony”, „cale” itp.) Są przechowywane w polu MS_Description właściwości rozszerzonych. Zastanawiam się, czy istnieje lepszy sposób na przechowywanie tych informacji. Przypuszczam, że jest to przydatne do celów dokumentacyjnych, ale trudno byłoby wykonać solidne obliczenia konwersji jednostek na podstawie tych danych. W tym momencie nie jestem gotowy na dokonanie inwazyjnej zmiany, ale jeśli mam taką możliwość, jaka jest zalecana najlepsza praktyka w tym zakresie? Opcje z góry mojej głowy mogą obejmować:

• Zmień nazwę kolumny na włączone jednostki (np. „TotalVolumeInGallons”. Dzięki temu informacje będą nieco łatwiej dostępne, ale nadal wydaje mi się słaba.)
• Dodaj osobną kolumnę „Jednostki”, aby odpowiadała każdej kolumnie „Kwota” (ta kolumna może być nvarchar LUB może być kluczem obcym do oddzielnej tabeli Jednostek, co może ułatwić obliczenie konwersji jednostek. Z drugiej strony, dodaj tak wiele kolumn może podwoić rozmiar mojej bazy danych - przy strasznie redundantnych danych).
• Utwórz nowe pole w rozszerzonych właściwościach dedykowanych specjalnie dla jednostek. (Niestety nie sądzę, że może to być klucz obcy do tabeli jednostek).
• Czy pomijam inny pomysł?

AKTUALIZACJA: Po przeczytaniu odpowiedzi @Todda Everetta przyszło mi do głowy możliwe rozwiązanie, więc zamierzam odpowiedzieć na własne pytanie. (Patrz poniżej)

Ponieważ wspominasz o setkach kolumn, rozważę projekt EAV . Podczas gdy Joe Celko ostrzega przed tym , myślę, że może to mieć zastosowanie w twoim przypadku użycia. Wygląda na to, że wszystkie twoje „kwoty” są liczbami, więc unikniesz problemów z rzutowaniem, które opisuje Joe i potrzeby nadania każdej „wartości” ciągu. Będzie działać jeszcze lepiej, jeśli wszystkie kwoty są liczbami całkowitymi, ale może również działać, jeśli niektóre są dziesiętne. Biorąc pod uwagę Jednostki miary, możesz pójść o krok dalej i wdrożyć model stylu „uniwersalnego modelu danych” na podstawie tego artykułu Davida Haya, a także nakreślony w jego książce Wzory modelu danych: Konwencje myślenia. Ten model ma dodatkową zaletę polegającą na konfigurowaniu, które „kwoty” odnoszą się do których „rzeczy”, jeśli są potrzebne. Dodatkowym krokiem pokazanym w książce na stronie 162 jest tabela przeliczania jednostek miary, której można użyć do konwersji między różnymi jednostkami miary. Oto przykład:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

To mówi, że aby przekonwertować z Kg na Lb, pierwszym krokiem jest pomnożenie Kg przez 2,2. Istnieje również stała, jeśli konwersja musi również obejmować stałą wartość i możliwość tworzenia wielu kroków. Tak więc podczas konwersji powiedzmy Celsjusza na Fahrenheita mnożymy Celsjusza przez 1,8, a następnie dodajemy 32. Kluczem będzie klucz z UOM, do UOM i krok obliczania.

To są moje 2 centy. Mam nadzieję, że te referencje dadzą ci trochę do myślenia, jeśli kiedykolwiek będziesz mieć szansę na ponowne uruchomienie obecnego projektu.

Cała praca.

Zwróć uwagę, że w drugim przypadku nie można dodawać jabłek i pomarańczy, dlatego dane są wyjątkowo łatwo poddane błędnej interpretacji.

Należy również pamiętać, że konwersje nie mogą być bardzo bezpieczne i są podatne na błąd zaokrąglania, przepełnienie itp.

Ponadto występują problemy fizyczne, takie jak ciężar właściwy i temperatura. Konwersja 20 galonów wody na funty wymagałaby znajomości gęstości wody. Ale gęstość wody zmienia się wraz z temperaturą, więc może być konieczne poznanie gęstości równolegle do pomiaru lub temperatury w podobny sposób i zastosowanie współczynnika korekcji objętości.

W przypadku właściwości Rozszerzonych jest to dobre tylko dla dokumentacji - dobra nazwa kolumny jest lepsza dla dokumentacji. Problem z kolumną sugerowaną jako stała jednostka z nazwy polega na tym, że kończysz się w rogu, kiedy zmieniasz jednostki miary - nowy klient chce ropy w beczkach, a nie galonach - i byłoby dobrze, ponieważ ich dane są w własną bazę danych, ale nazwa kolumny wprowadza teraz w błąd.

Inną opcją jest przechowywanie wersji kanonicznych w stałych jednostkach (tj. Zawsze kilogramach i metrach) oprócz różnych oryginalnych pomiarów. Operacje agregacji na jednostkach stałych powinny być w porządku (z wyjątkiem, na przykład, nie dodawania temperatur), ale nie tracisz oryginalnego pomiaru.

Prostym rozwiązaniem, które w przeszłości sprawdziło się dla mnie dobrze, jest przechowywanie wszystkich danych w jednostkach „podstawowych”. Na przykład podstawa dla długości może być milimetrowa, a podstawa dla ciężarów może być kilogramami. To rozwiązanie może spowodować konieczność przekształcenia niektórych istniejących danych w jednostkę podstawową, jeśli jeszcze nie jest.

Po zgromadzeniu wszystkich danych w standardowych jednostkach bazowych nie ma potrzeby przechowywania jednostki w samej bazie danych, ponieważ jest to obecnie założenie systemowe. Wyświetlane jednostki wymagane dla każdego typu jednostki (np. Czy wyświetlać mm, cale, cm, m dla długości) stają się problemem aplikacji / domeny klienta, który można zapisać w pamięci lokalnej.

Tabele konwersji jednostek do konwersji między różnymi obsługiwanymi jednostkami można zapisać na stałe w aplikacji, ponieważ nowe jednostki miary zmieniają się niezwykle rzadko.

NB pokrewnym rozwiązaniem innego problemu jest to, że podczas przechowywania znaczników czasu w bazie danych należy zawsze przechowywać je w jednostce „podstawowej” - UTC .

Kolejne powiązane pytania i odpowiedzi na ten temat ...

• /programming/12977021/best-practice-for-storing-weights-in-a-sql-database

• Zawiera kilka dobrych informacji na temat tego, dlaczego stosowanie typu kolumny zmiennoprzecinkowej jest najlepszym sposobem na przechowywanie pomiarów w świecie rzeczywistym.

Ponieważ dowolną jednostkę można przekształcić w inną jednostkę tego samego typu Za pomocą formuły:

y = ((x + xOffset) * multiplicand / denominator) + yOffset

Stworzyłbym tabelę zawierającą typy jednostek plus te 4 wartości.

From Unit     To Unit      Unit Type    From Offset    Multiplicand    Denominator    To Offset
'milligrams'  'grams'      'mass'       0              1               1000           0
'grams'      'kilograms'   'mass'       0              1               1000           0
'grams'      'ounces'      'mass'       0              100000          2835           0
'ounces'     'pound'       'mass'       0              1               16             0

Po dodaniu wszystkich pomiarów, na które prawdopodobnie chcesz przekonwertować, znajdują się po obu stronach listy, uruchom zapytanie, w którym wstawiasz operację odwrotną, po prostu negując przesunięcia i zamieniając multiplikację i mianownik oraz na jednostkę i na jednostkę.

Aby dodać konwersję między wszystkimi typami, sprzężenie krzyżowe Z niektórymi filtrami może wstawić pozostałe konwersje.

Po przeczytaniu odpowiedzi @Todda Everetta przyszło mi do głowy rozwiązanie, więc zamierzam odpowiedzieć na własne pytanie. Co myślę, że mam zamiar zrobić, to utworzyć oddzielną ColumnUnitstabelę z czterema kolumnami: Schema, Table, Column, UnitsID(gdzie UnitsID jest FK do osobnej UnitsOfMeasuretabeli), w ten sposób mapowania dowolną kolumnę jego powiązanej jednostki miary. Oczywiście największym minusem tego pomysłu jest to, że programiści musieliby pamiętać, aby edytować tę tabelę za każdym razem, gdy zmieniają nazwę kolumny lub tabeli [ może użyć wyzwalacza DDL ? ], w przeciwnym razie system się zepsuje. Ale zakładając, że takie zmiany nazw są rzadkie, a sklep deweloperski mały (w moim przypadku tylko jedna osoba), ta architektura powinna być wykonalna. Zaletą jest to, że nie trzeba wprowadzać żadnych inwazyjnych zmian w bieżącym DB, a ja muszę przechowywać wartość tylko raz dla każdej kolumny, a nie raz na wiersz, jak wymagałaby to moja druga opcja w moim oryginalnym poście.