Jak obsługiwać projekt tabeli za pomocą zmiennych kolumn

Mam scenariusz projektowania tabeli i jako typ inny niż DBA, chciałbym opinii, w których jest bardziej skalowalny.

Powiedzmy, że jesteś proszony o zapisanie informacji o domach dla obszaru metra, zaczynając od małej dzielnicy (200 domów), ale ostatecznie rosnąc do 5000000+ domów.

Wymagane jest przechowywanie podstawowych informacji: ID # (unikalna partia #, której możemy użyć jako unikalnego indeksu), Addr, City, State, Zip. Poradzi sobie z tym prosty, prosty stół.

Ale każdego roku zostaniesz poproszony o zapisanie dodatkowych informacji o wszystkich domach - i CO te informacje będą się zmieniać każdego roku. Na przykład w pierwszym roku zostaniesz poproszony o nagranie nazwiska właściciela i materiału kwadratowego. W drugim roku zostaniesz poproszony o zachowanie nazwiska, ale zrzuć kwadratowy materiał i zacznij zbierać imiona właścicieli.

Wreszcie - co roku zmieni się liczba dodatkowych kolumn. Może zacznij od 2 dodatkowych kolumn, następnie przejdź do 6 w przyszłym roku, a następnie z powrotem do 2.

Tak więc jednym podejściem do tabeli jest próba dodania niestandardowych informacji jako kolumn w tabelach domów, aby istniała tylko jedna tabela.

Ale mam sytuację, w której ktoś rozłożył tabele w tym celu:

Kolumny „Tabela domu”: ID, Adres, Miasto, Stan, Kod pocztowy - z jednym rzędem na dom

ID   Addr              City     State  Zip 
-------------------------------------------
1    10 Maple Street   Boston      MA  11203

2    144 South Street  Chelmsford  MA  11304

3    1 Main Avenue     Lowell      MA  11280

Kolumny „Tabela informacji niestandardowych”: identyfikator, nazwa, wartość - z tabelą wyglądającą jak:

ID   Name             Value

1    Last Name        Smith

2    Last Name        Harrison

3    Last Name        Markey

1    Square Footage   1200

2    Square Footage   1930

3    Square Footage 

Istnieje więc wiele wierszy dla każdego rekordu domu. Każdego roku, gdy wymagane są opcjonalne informacje, ta tabela jest dosłownie przebudowywana, więc w przyszłym roku może wyglądać następująco:

1    Last Name    Smith

2    Last Name    Harrison

3    Last Name    Markey

1    First Name   John

2    First Name   Harry

3    First Name   Jim

W końcu gromadzisz 100 000 rzędów domów ORAZ w ciągu roku pojawia się 10 dodatkowych informacji; druga tabela zawiera teraz 1 000 000 wierszy informacji, z których wiele zawiera informacje nadmiarowe (opisowe). Ogólne wymagania dotyczące bazy danych są takie, że ludzie będą musieli uzyskać informacje o wierszu domu + powiązane wartości pól niestandardowych tysiące razy dziennie.

Więc moje pytanie: czy byłoby złym (lub okropnym) ćwiczeniem zamiast tego:

A) Ułóż tabelę domu, zgadując przy maksymalnej liczbie niestandardowych kolumn (zwanych być może „1” do „10”) i wstaw te niestandardowe wartości bezpośrednio w rzędach domu

LUB

B) Przechowuj niestandardowe informacje w tabeli domu, ale każdego roku, gdy zmieniają się wymagania, odbuduj tabelę domu, używając tylko # kolumn potrzebnych do niestandardowych informacji, z myślą, że wymagania mogą się zwariować i nigdy nie wiesz, ile maksimum opcjonalne pola mogą być wymagane?

Dzięki, mam nadzieję, że to ma sens!

Masz prawie 4 możliwości:

NoSQL - definicja Każdy rekord jest przechowywany jako zestaw par Klucz / Wartość. Jest bardzo elastyczny i szybki. Nie wszyscy autorzy raportów obsługują ten styl przechowywania. Istnieje wiele przykładowych implementacji bazy danych NoSQL. Tym, który wydaje się obecnie najbardziej popularny, jest MongoDB.

EAV - definicja W tym miejscu należy obrócić cały stół lub porcję (w innym stole) na bok. Jest to dobry wybór, jeśli masz już wewnętrzną relacyjną bazę danych, z której nie można łatwo się wycofać. Podany przykład niestandardowej tabeli informacyjnej jest dobrym przykładem tabeli EAV.

Standardowe tabele z kolumnami XML - Pomyśl o tym, ponieważ NoSQL spełnia tabele relacyjne. Dane przechowywane w kolumnie XML mogą mieć dowolny format obsługiwany przez XML, w tym wiele skorelowanych subdanych. W przypadku kolumn, o których wiesz, że będą to „zwykłe” kolumny, można je zbudować jako odpowiedni typ kolumny do przechowywania danych (nazwisko, adres, miasto, stan itp.).

Standardowe tabele z dużą ilością dodatkowych kolumn - Masz relacyjną bazę danych, nie możesz używać XML ani EAV, a NoSQL nie jest opcją. Dodaj wiele dodatkowych kolumn każdego typu. Domyślam się, że 30 lub więcej varchar, 30 lub więcej liczb całkowitych, 15 lub więcej liczb. A kiedy użyjesz kolumny dla wartości, nie używaj jej ponownie . I nie usuwaj też kolumny .

Ze wszystkich tych rozwiązań, moim zdaniem, przekonasz się, że podejście NoSQL lub EAV będzie najbardziej skuteczne przy najmniejszej ilości refaktoryzacji kodu i schematu.

Będziesz mieć sytuację, w której zbierzesz dane przez jeden rok, a nie w następnym, a następnie zbierzesz je później. Próba aktualizacji starszych danych o prawidłowe informacje jest problematyczna i kosztowna. Przechowywanie nie jest ani.

Aby odpowiedzieć na twoje pytanie dotyczące tych dwóch opcji, żadna z nich nie wydaje mi się słuszna. A) zablokuje cię, a B) to dużo pracy. Obecny schemat, który opisujesz, nie jest taki zły (z wyjątkiem tego, że nazwa informacji („imię”, „stopa kwadratowa” itp.) Jest ciągiem zamiast identyfikatora odwołującego się do tabeli odnośników.

Wydaje mi się to jednak dobrym kandydatem do bazy danych NoSQL ( http://en.wikipedia.org/wiki/NoSQL ). Chociaż nigdy nie pracowałem z taką bazą danych, opisujesz typowy scenariusz, który to rozwiązuje.

Jeśli współbieżna liczba kolumn niestandardowych jest skończona, a limity są znane (np. Nie więcej niż 10-20 kolumn niestandardowych dla ciągów, nie więcej niż x kolumn dla liczb całkowitych itp.)
Możesz użyć tabeli podstawowej z dodatkowymi polami dla każdego typu danych i zamiast tego z corocznej przebudowy tabeli utwórz widok na ten rok, zawierający tylko odpowiednie kolumny niestandardowe i zmień nazwę pól ogólnych, aby odzwierciedlić zawartość dla tego roku.

House Table:
ID, Addr, City, State, Zip, custom_string1,cs_2,cs_3,custom_integer_1,ci_2,ci_3 ...

create view house_2014 as 
select ID, Addr, City, State, Zip,
custom_string1 as last_name,cs_2 as first_name ...

Problem z tym podejściem polega na tym, że nie masz historii, ale możesz łatwo zrobić kopię każdego roku przed zmianą wymagań kolumny.

create table house_2014_archive as select * from house_2014;
drop house_2014;
create view house_2015 as "select column list for new year";

Czy potrafisz wymienić wszystkie scenariusze, dla których chcesz przechowywać te dane?

jeśli istnieje skończona liczba kombinacji kolumn, które można zastosować do tabeli, spróbuj modelować „tabelę podstawową” ze zwykłymi kolumnami, które będą stosowane w przypadku wszystkich scenariuszy, a następnie utwórz więcej tabel (w celu zaimplementowania pewnego rodzaju dziedziczenia; jest to znane jako podtyp / nadtyp w ERD i projektowaniu bazy danych.)

jedna tabela dla każdego scenariusza, w ten sposób przynajmniej utrzymasz tabele w czystości i będziesz mógł uniknąć przechowywania adresu ulicy w kolumnie „nazwisko” ...

spójrz na to pytanie projektowe: /programming/554522/something-like-inheritance-in-database-design