Dlaczego relacyjne bazy danych nie obsługują zwracania informacji w formacie zagnieżdżonym?

Załóżmy, że tworzę bloga, w którym chcę mieć posty i komentarze. Tworzę więc dwie tabele, tabelę „posty” z kolumną „id” z automatyczną inkrementacją liczb całkowitych i tabelę „komentarzy” z kluczem obcym „post_id”.

Następnie chcę uruchomić to, co prawdopodobnie będzie moim najczęstszym zapytaniem, czyli pobrać post i wszystkie jego komentarze. Będąc dość nowym w relacyjnych bazach danych, podejście, które wydaje mi się najbardziej oczywiste, polega na napisaniu zapytania, które wyglądałoby mniej więcej tak:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Który dałby mi identyfikator i treść posta, który chcę, wraz ze wszystkimi odpowiednimi wierszami komentarzy spakowanymi starannie w tablicy (zagnieżdżona reprezentacja, taka jak w JSON). Oczywiście SQL i relacyjne bazy danych nie działają w ten sposób, a najbliższe, co mogą uzyskać, to połączenie między „postami” i „komentarzami”, które zwrócą wiele niepotrzebnego powielania danych (z powtarzaniem tych samych informacji o postach w każdym wierszu), co oznacza, że ​​czas przetwarzania jest spędzany zarówno w bazie danych, aby zebrać wszystko razem, jak i na mojej ORM, aby przeanalizować i cofnąć wszystko.

Nawet jeśli poinstruuję mój ORM, aby chętnie ładował komentarze do posta, najlepiej będzie wysłać jedno zapytanie do posta, a następnie drugie zapytanie, aby pobrać wszystkie komentarze, a następnie połączyć je po stronie klienta, co jest również nieefektywny.

Rozumiem, że relacyjne bazy danych są sprawdzoną technologią (do diabła, są starsze ode mnie) i że przez dziesięciolecia przeprowadzono w nich mnóstwo badań i jestem pewien, że istnieje naprawdę dobry powód, dla którego oni (i Standard SQL) są zaprojektowane tak, aby działały tak, jak działają, ale nie jestem pewien, dlaczego opisane powyżej podejście nie jest możliwe. Wydaje mi się, że jest to najprostszy i najbardziej oczywisty sposób na wdrożenie jednej z najbardziej podstawowych relacji między rekordami. Dlaczego relacyjne bazy danych nie oferują czegoś takiego?

(Uwaga: głównie piszę aplikacje internetowe przy użyciu magazynów danych Rails i NoSQL, ale ostatnio wypróbowałem Postgres i bardzo mi się podoba. Nie chcę atakować relacyjnych baz danych, po prostu jestem zakłopotany.)

Nie pytam, jak zoptymalizować aplikację Rails ani jak zhakować ten problem w konkretnej bazie danych. Pytam, dlaczego standard SQL działa w ten sposób, gdy wydaje mi się sprzeczny z intuicją i marnotrawstwem. Musi być jakiś historyczny powód, dla którego oryginalni projektanci SQL chcieli, aby ich wyniki wyglądały tak.

CJ Date szczegółowo omawia to w rozdziale 7 i załączniku B do SQL i teorii relacyjnej . Masz rację, w teorii relacji nie ma niczego, co zabraniałoby typowi danych atrybutu bycia relacją, o ile jest to ten sam typ relacji w każdym wierszu. Twój przykład się kwalifikuje.

Ale Date mówi, że takie struktury są „zwykle - ale nie zawsze - przeciwwskazane” (tzn. Zły pomysł), ponieważ hierarchie relacji są asymetryczne . Na przykład transformacja ze struktury zagnieżdżonej do znanej „płaskiej” struktury nie zawsze może zostać odwrócona w celu odtworzenia zagnieżdżenia.

Zapytania, ograniczenia i aktualizacje są bardziej złożone, trudniejsze do napisania i trudniejsze do obsługi RDBMS, jeśli zezwolisz na atrybuty o wartości relacyjnej (RVA).

Utrudnia również zasady projektowania baz danych, ponieważ najlepsza hierarchia relacji nie jest tak jasna. Czy powinniśmy zaprojektować relację Dostawców z zagnieżdżoną RVA dla części dostarczanych przez danego Dostawcę? Czy relacja Części z zagnieżdżoną RVA dla dostawców, którzy dostarczają daną Część? Lub przechowuj oba, aby ułatwić uruchamianie różnego rodzaju zapytań?

Jest to ten sam dylemat, który wynika z hierarchicznej bazy danych i modeli baz danych zorientowanych na dokumenty . W końcu złożoność i koszt dostępu do zagnieżdżonych struktur danych skłaniają projektantów do nadmiarowego przechowywania danych w celu łatwiejszego przeszukiwania różnych zapytań. Model relacyjny zniechęca do redundancji, więc RVA mogą działać wbrew celom modelowania relacyjnego.

Z tego, co rozumiem (nie korzystałem z nich), Rel i Dataphor to projekty RDBMS, które obsługują atrybuty cenione w relacjach.

Ponownie skomentuj @dportas:

Typy strukturalne są częścią SQL-99, a Oracle je obsługuje. Ale nie przechowują wielu krotek w zagnieżdżonej tabeli na wiersz tabeli podstawowej. Typowym przykładem jest atrybut „adres”, który wydaje się być pojedynczą kolumną tabeli podstawowej, ale zawiera dodatkowe podkolumny z ulicami, miastami, kodem pocztowym itp.

Tabele zagnieżdżone są również obsługiwane przez Oracle i pozwalają one na wiele krotek na wiersz tabeli podstawowej. Ale nie wiem, czy jest to część standardowego SQL. I pamiętaj o konkluzjach jednego z blogów: „Nigdy nie użyję tabeli zagnieżdżonej w instrukcji CREATE TABLE. Spędzasz cały swój czas ODCZESTUJĄC je, aby znów były użyteczne!”

Niektóre z najwcześniejszych systemów baz danych były oparte na modelu hierarchicznej bazy danych . To reprezentowało dane w strukturze drzewa z rodzicem i dziećmi, podobnie jak sugerujesz tutaj. HDMS zostały w dużej mierze zastąpione przez bazy danych zbudowane na modelu relacyjnym. Głównymi powodami tego było to, że RDBMS mógł modelować relacje „wiele do wielu”, które były trudne dla hierarchicznych baz danych, i że RDBMS mógł łatwo wykonywać zapytania, które nie były częścią pierwotnego projektu, podczas gdy HDBMS ograniczał cię do zapytania ścieżkami określonymi w czasie projektowania.

Wciąż istnieje kilka przykładów hierarchicznych systemów baz danych, w szczególności rejestr systemu Windows i LDAP.

Obszerne omówienie tego tematu jest dostępne w następnym artykule

Przypuszczam, że twoje pytanie naprawdę koncentruje się na fakcie, że chociaż bazy danych opierają się na solidnej logice i ustawiają podstawy termoretyczne i wykonują bardzo dobrą pracę, przechowując, przetwarzając i wyszukując dane w (2-wymiarowych) zestawach, zapewniając jednocześnie integralność referencyjną, współbieżność i wiele innych rzeczy, nie zapewniają one (dodatkowej) funkcji wysyłania (i odbierania) danych w tak zwanym formacie obiektowym lub hierarchicznym.

Następnie twierdzisz, że „nawet jeśli poinstruuję mój ORM, aby chętnie ładował komentarze do posta, najlepiej będzie wysłać jedno zapytanie do posta, a następnie drugie zapytanie, aby pobrać wszystkie komentarze, a następnie złożyć je razem po stronie klienta, co również jest nieefektywne ” .

Nie widzę nic nieefektywnego w wysyłaniu 2 zapytań i otrzymywaniu 2 partii wyników z:

--- Query-1-posts
SELECT id, content 
FROM posts
WHERE id = 7


--- Query-2-comments
SELECT * 
FROM comments 
WHERE post_id = 7

Twierdzę, że jest to (prawie) najbardziej wydajny sposób (prawie, ponieważ tak naprawdę nie potrzebujesz posts.idi nie wszystkie kolumny z comments.*)

Jak zauważył Todd w swoim komentarzu, nie należy prosić bazy danych o zwrócenie danych gotowych do wyświetlenia. Jest to zadanie aplikacji. Możesz napisać (jedno lub kilka) zapytań, aby uzyskać wyniki potrzebne dla każdej operacji wyświetlania, aby nie było niepotrzebnego powielania danych przesyłanych przewodem (lub magistralą pamięci) z bazy danych do aplikacji.

Nie mogę tak naprawdę mówić o ORM, ale być może niektórzy z nich mogą wykonać dla nas część tej pracy.

Podobne techniki mogą być stosowane w dostarczaniu danych między serwerem internetowym a klientem. Stosowane są inne techniki (takie jak buforowanie), aby baza danych (lub serwer internetowy lub inny) nie była przeciążona zduplikowanymi żądaniami.

Domyślam się, że standardy, takie jak SQL, są najlepsze, jeśli pozostają wyspecjalizowane w jednym obszarze i nie próbują objąć wszystkich obszarów pola.

Z drugiej strony komitet, który ustala standard SQL, może w przyszłości pomyśleć inaczej i zapewnić standaryzację dla takiej dodatkowej funkcji. Ale nie jest to coś, co można zaprojektować w ciągu jednej nocy.

Nie jestem w stanie odpowiedzieć prawidłową, argumentowaną odpowiedzią, więc proszę, głosujcie w zapomnienie, jeśli się mylę (ale proszę mnie poprawić, abyśmy mogli nauczyć się czegoś nowego). Myślę, że powodem jest to, że relacyjne bazy danych są skoncentrowane na modelu relacyjnym, który z kolei opiera się na czymś, o czym nic nie wiem, zwanym „logiką pierwszego rzędu”. To, o co możesz zapytać, prawdopodobnie nie pasuje koncepcyjnie do matematyczno-logicznej struktury, na której zbudowane są relacyjne bazy danych. Co więcej, to, o co pytasz, jest zazwyczaj łatwe do rozwiązania za pomocą baz danych graficznych, co daje więcej wskazówek, że to podstawowa konceptualizacja bazy danych jest sprzeczna z tym, co chcesz osiągnąć.

Wiem, że przynajmniej SQLServer obsługuje zagnieżdżone zapytania, gdy używasz FOR XML.

SELECT id, content, (SELECT * FROM comments WHERE post_id = posts.id FOR XML PATH('comments'), TYPE) AS comments
FROM posts
WHERE id = 7
FOR XML PATH('posts')

Problemem tutaj nie jest brak obsługi RDBMS, ale brak obsługi zagnieżdżonych tabel w tabelach.

Poza tym, co powstrzymuje cię przed użyciem połączenia wewnętrznego?

SELECT id, content, comments.*
FROM posts inner join comments on comments.post_id = posts.id
WHERE id = 7

Możesz faktycznie spojrzeć na łączenie wewnętrzne jako tabelę zagnieżdżoną, tylko zawartość pierwszych 2 pól jest powtarzana w określonym czasie. Nie martwiłbym się zbytnio wydajnością łączenia, jedyną wolną częścią takiego zapytania jest io od bazy danych do klienta. Będzie to stanowić problem tylko wtedy, gdy zawartość zawiera dużą ilość danych. W takim przypadku sugerowałbym dwa zapytania, jedno z wewnętrznym select id, contenti jedno z wewnętrznym złączeniem i select posts.id, comments.*. Skaluje się nawet w przypadku wielu postów, ponieważ nadal używasz tylko 2 zapytań.

W rzeczywistości Oracle obsługuje to, co chcesz, ale musisz zawrzeć zapytanie podrzędne słowem kluczowym „kursor”. Wyniki są pobierane za pomocą otwartego kursora. Na przykład w Javie komentarze pojawiałyby się jako zestawy wyników. Więcej informacji na ten temat można znaleźć w dokumentacji Oracle na temat „CURSOR Expression”

SELECT id, content, cursor(SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Niektóre obsługują zagnieżdżanie (hierarchiczne).

Jeśli chcesz jedno zapytanie, możesz mieć jedną tabelę, która sama się odwołuje. Niektóre RDMS obsługują tę koncepcję. Na przykład za pomocą programu SQL Server można użyć typowych wyrażeń tabelowych (CTE) dla zapytania hierarchicznego.

W twoim przypadku posty będą na poziomie 0, a następnie wszystkie komentarze będą na poziomie 1.

Inne opcje to 2 zapytania lub Łączenie z dodatkowymi informacjami dla każdego zwracanego rekordu (o którym wspominali inni).

Przykład hierarchicznej:

https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example

W powyższym linku EmpLevel pokazuje poziom zagnieżdżenia (lub hierarchii).

Przepraszam, nie jestem pewien, czy dokładnie rozumiem twój problem.

W MSSQL możesz po prostu wykonać 2 instrukcje SQL.

SELECT id, content
FROM posts
WHERE id = 7

SELECT * FROM comments WHERE post_id = 7

I zwróci jednocześnie 2 zestawy wyników.

RDBM są oparte na teorii i trzymają się teorii. Pozwala to na pewną spójność i sprawdzoną matematycznie niezawodność.

Ponieważ model jest prosty i ponownie oparty na teorii, ułatwia ludziom optymalizację i wiele implementacji. W przeciwieństwie do NoSQL, gdzie każdy robi to nieco inaczej.

W przeszłości podejmowano próby stworzenia hierarchicznych baz danych, ale IIRC (nie wydaje się google go mieć) pojawiły się problemy (przychodzą na myśl cykle i równość).

Masz konkretną potrzebę. Najlepiej byłoby wyodrębnić dane z bazy danych w wybranym formacie, abyś mógł zrobić z tym, co chcesz.

Niektóre bazy danych nie radzą sobie tak dobrze, ale i tak nie jest niemożliwością ich zbudowanie. Pozostawienie formatowania innym aplikacjom jest obecnym zaleceniem, ale nie uzasadnia, dlaczego nie można tego zrobić.

Jedynym argumentem, który mam przeciwko twojej sugestii, jest możliwość obsługi tego zestawu wyników w sposób „sql”. Byłoby złym pomysłem stworzyć wynik w bazie danych i nie być w stanie z nią pracować ani do pewnego stopnia manipulować. Powiedzmy, że utworzyłem widok zbudowany zgodnie z twoimi sugestiami, jak zawrzeć go w innej instrukcji select? Bazy danych lubią brać wyniki i robić z nimi różne rzeczy. Jak mógłbym dołączyć do innego stołu? Jak porównałbym twój zestaw wyników do innego?

Zatem zaletą RDMS jest elastyczność SQL. Składnia wybierania danych z tabeli jest bardzo zbliżona do listy użytkowników lub innych obiektów w systemie (przynajmniej taki jest cel). Nie jestem pewien, czy warto robić coś zupełnie innego. Nie doprowadzili ich nawet do bardzo skutecznego przetwarzania kodu / kursorów lub BLOBS danych.

Moim zdaniem wynika to głównie z SQL i sposobu wykonywania zapytań agregujących - funkcje agregujące i grupowanie są wykonywane na dużych dwuwymiarowych zestawach wierszy w celu zwrócenia wyników. Tak było od samego początku i jest bardzo szybki (większość rozwiązań NoSQL jest dość powolna w agregacji i opiera się na zdenormalizowanym schemacie zamiast złożonych zapytań)

Oczywiście PostgreSQL ma pewne funkcje z obiektowej bazy danych. Zgodnie z tą wiadomością ( wiadomością ) możesz osiągnąć to, czego potrzebujesz, tworząc niestandardową agregację.

Osobiście używam frameworków takich jak Doctrine ORM (PHP), które wykonują agregację po stronie aplikacji i obsługują takie funkcje, jak leniwe ładowanie w celu zwiększenia wydajności.

PostgreSQL obsługuje wiele strukturalnych typów danych, w tym tablice i JSON . Za pomocą SQL lub jednego z wbudowanych języków proceduralnych można budować wartości o dowolnej strukturze i zwracać je do aplikacji. Możesz także tworzyć tabele z kolumnami dowolnego typu strukturalnego, ale powinieneś dokładnie rozważyć, czy niepotrzebnie denormalizujesz swój projekt.