MySQL: wiele tabel czy jedna tabela z wieloma kolumnami?

Więc to jest bardziej kwestia projektowa.

Mam jeden klucz podstawowy (powiedzmy identyfikator użytkownika) i mam mnóstwo informacji powiązanych z tym użytkownikiem.

Czy powinienem mieć wiele tabel podzielonych na kategorie zgodnie z informacjami, czy tylko jedną tabelę z wieloma kolumnami?

Sposób, w jaki to robiłem, polegał na tym, że miałem wiele tabel, na przykład jedna tabela na dane o użytkowaniu aplikacji, jedna tabela na informacje o profilu, jedna tabela na tokeny zaplecza itp., Aby wszystko wyglądało uporządkowane.

Niedawno ktoś mi powiedział, że lepiej tego nie robić w ten sposób, a tabela z dużą ilością kolumn jest w porządku. Chodzi o to, że wszystkie te kolumny mają ten sam klucz podstawowy.

Jestem całkiem nowy w projektowaniu baz danych, więc które podejście jest lepsze i jakie są wady i zalety?

Jaki jest konwencjonalny sposób na to?

Za każdym razem, gdy informacja jest jeden do jednego (każdy użytkownik ma jedną nazwę i hasło), prawdopodobnie lepiej jest mieć jedną tabelę, ponieważ zmniejsza to liczbę złączeń, które baza danych będzie musiała wykonać, aby pobrać wyniki. Myślę, że niektóre bazy danych mają ograniczenie liczby kolumn w tabeli, ale w normalnych przypadkach nie martwiłbym się tym i zawsze możesz podzielić to później, jeśli zajdzie taka potrzeba.

Jeśli dane są typu jeden do wielu (każdy użytkownik ma tysiące wierszy informacji o użytkowaniu), należy je podzielić na osobne tabele, aby zmniejszyć liczbę duplikatów danych (zduplikowane dane marnują przestrzeń dyskową, pamięć podręczną i sprawiają, że baza danych jest trudniejsza w utrzymaniu ).

Artykuł w Wikipedii dotyczący normalizacji baz danych może być interesujący, ponieważ szczegółowo omawia przyczyny takiego stanu rzeczy:

Normalizacja bazy danych to proces organizowania pól i tabel relacyjnej bazy danych w celu zminimalizowania nadmiarowości i zależności. Normalizacja zwykle polega na podzieleniu dużych tabel na mniejsze (i mniej nadmiarowe) tabele i zdefiniowaniu relacji między nimi. Celem jest wyodrębnienie danych, tak aby dodawanie, usuwanie i modyfikacje pola można było wprowadzać w jednej tabeli, a następnie propagować w pozostałej części bazy danych za pośrednictwem zdefiniowanych relacji.

Należy również pamiętać o denormalizacji , ponieważ są przypadki, w których powtarzanie danych jest lepsze (ponieważ zmniejsza ilość pracy, jaką musi wykonać baza danych podczas odczytu danych). Zdecydowanie zalecam, aby na początku Twoje dane były jak najbardziej znormalizowane i denormalizuj tylko wtedy, gdy masz świadomość problemów z wydajnością w określonych zapytaniach.

Jeden duży stół to często kiepski wybór. Powiązane tabele to relacyjna baza danych, z którą została zaprojektowana. Jeśli indeksujesz poprawnie i wiesz, jak pisać wydajne zapytania, będą działać dobrze.

Gdy tabele zawierają zbyt wiele kolumn, mogą wystąpić problemy z rzeczywistym rozmiarem strony, na której baza danych przechowuje informacje. Albo rekord może okazać się zbyt duży dla strony, co może spowodować, że nie będziesz w stanie utworzyć lub zaktualizować określonego rekordu, co sprawia, że ​​użytkownicy są niezadowoleni, lub możesz (przynajmniej w SQL Server) zezwolić na pewne przepełnienie dla konkretnego typy danych (z zestawem reguł musisz sprawdzić, jeśli to robisz), ale jeśli wiele rekordów przekroczy rozmiar strony, możesz spowodować ogromne problemy z wydajnością. Teraz, jak MYSQL radzi sobie ze stronami i czy masz problem, gdy potencjalny rozmiar strony staje się zbyt duży, musisz sprawdzić w dokumentacji tej bazy danych.

Mam dobry przykład. Zbyt znormalizowana baza danych z następującym zestawem relacji:

people -> rel_p2staff -> staff

i

people -> rel_p2prosp -> prospects

Tam, gdzie ludzie mają imiona i dane o osobach, pracownicy mają tylko dane dotyczące rekordów personelu, potencjalni klienci mają tylko szczegóły dotyczące potencjalnych klientów, a tabele rel to tabele relacji z obcymi kluczami od osób łączących się z personelem i potencjalnymi klientami.

Ten rodzaj projektu dotyczy całej bazy danych.

Teraz, aby odpytać ten zestaw relacji, jest to łączenie wielotabelowe za każdym razem, czasami łączenie 8 i więcej tabel. Działało dobrze do połowy tego roku, kiedy zaczęło działać bardzo wolno teraz, gdy przekroczyliśmy 40000 rekordów ludzi.

Indeksowanie i wszystkie nisko wiszące owoce zostały zużyte w zeszłym roku, wszystkie zapytania są zoptymalizowane do perfekcji. To koniec drogi do konkretnego znormalizowanego projektu, a zarząd zatwierdził przebudowę całej aplikacji od niego zależnej, a także restrukturyzację bazy danych na okres 6 miesięcy. $$$$ Ouch.

Rozwiązaniem będzie mieć bezpośredni związek dla people -> staffipeople -> prospect

Przeszedł przez to i jako ktoś, kto często korzystał z MySQL, a następnie niedawno przeszedł na Postgres, jedną z największych zalet jest to, że możesz dodawać obiekty JSON do pola w Postgres.

Więc jeśli jesteś w tej sytuacji, nie musisz koniecznie decydować między jedną dużą tabelą z wieloma kolumnami i dzieleniem jej, ale możesz scalić kolumny w obiekty JSON, aby je zmniejszyć, np. Zamiast adresowania być 5 kolumnami, może po prostu być jednością. Możesz również zapytać o ten obiekt.

zadaj sobie te pytania, jeśli umieścisz wszystko w jednej tabeli, czy będziesz mieć wiele wierszy dla tego użytkownika? Jeśli musisz zaktualizować użytkownika, czy chcesz zachować ścieżkę audytu? Czy użytkownik może mieć więcej niż jedną instancję elementu danych? (na przykład numer telefonu) czy będziesz mieć przypadek, w którym mógłbyś chcieć później dodać element lub zestaw elementów? jeśli odpowiesz tak, najprawdopodobniej chcesz mieć tabele podrzędne z relacjami kluczy obcych.

Zalety tabel nadrzędnych / podrzędnych to integralność danych, wydajność za pomocą indeksów (tak, możesz to zrobić również na płaskiej tabeli) i łatwiejsze w utrzymaniu IMO, jeśli musisz później dodać pole, zwłaszcza jeśli będzie to pole wymagane.

Projektowanie wad jest trudniejsze, zapytania stają się nieco bardziej złożone

Ale jest wiele przypadków, w których jeden duży płaski stół będzie odpowiedni, więc aby podjąć decyzję, musisz spojrzeć na swoją sytuację.

Skończyłem już projektować bazę danych. dla mnie zależy to od stopnia trudności systemu z zarządzaniem bazą danych; tak, to prawda, że ​​unikalne dane znajdują się tylko w jednym miejscu, ale naprawdę trudno jest tworzyć zapytania z nadmiernie znormalizowaną bazą danych z dużą ilością rekordów. Po prostu połącz oba schematy; użyj jednej ogromnej tabeli, jeśli czujesz, że będziesz mieć ogromne rekordy, które są trudne do utrzymania, tak jak Facebook, Gmail itp. i użyj innej tabeli dla jednego zestawu rekordów dla prostego systemu ... cóż, to tylko moja opinia .. mam nadzieję, że może to pomóc ... po prostu to zrób ... możesz to zrobić ... :)

Konwencjonalnym sposobem byłoby użycie różnych tabel, takich jak schemat gwiazdy lub schemat płatka śniegu. Jednak oparłbym tę strategię na podwójnej. Wierzę w teorię, że dane powinny istnieć tylko w jednym miejscu, tam schemat, o którym wspomniałem, działałby dobrze. Uważam jednak również, że w przypadku silników raportowania i pakietów BI podejście kolumnowe byłoby niezwykle korzystne, ponieważ jest bardziej zgodne z potrzebami raportowania. Podejścia kolumnowe, takie jak te z infobright.org, zapewniają ogromny wzrost wydajności i kompresję, co sprawia, że ​​użycie obu podejść jest niezwykle przydatne. Wiele firm zaczyna zdawać sobie sprawę, że tylko jedna architektura bazy danych w organizacji nie jest w stanie zaspokoić pełnego zakresu ich potrzeb. Wiele firm wdraża obie koncepcje posiadania więcej niż jednej architektury bazodanowej.

Myślę, że posiadanie pojedynczej tabeli jest bardziej efektywne, ale powinieneś upewnić się, że tabela jest zorganizowana w sposób, który pokazuje związek, trend, a także różnicę w zmiennych w tym samym wierszu. na przykład, jeśli tabela pokazuje wiek i stopnie uczniów, należy ustawić tabelę w taki sposób, aby dzięki temu, że osoba uzyskująca najwyższy wynik była dobrze zróżnicowana, a różnica wieku uczniów była równa.