Jak zaprojektować tabelę produktów dla wielu rodzajów produktów, w których każdy produkt ma wiele parametrów

Nie mam dużego doświadczenia w projektowaniu stołów. Moim celem jest utworzenie jednej lub więcej tabel produktów, które spełniają poniższe wymagania:

• Obsługa wielu rodzajów produktów (telewizor, telefon, komputer, ...). Każdy rodzaj produktu ma inny zestaw parametrów, takich jak:

  • Telefon będzie miał kolor, rozmiar, wagę, system operacyjny ...

  • Komputer będzie miał procesor, dysk twardy, pamięć RAM ...

• Zestaw parametrów musi być dynamiczny. Możesz dodać lub edytować dowolny parametr.

Jak mogę spełnić te wymagania bez osobnej tabeli dla każdego rodzaju produktu?

Masz co najmniej pięć opcji modelowania opisywanej hierarchii typów:

• Dziedziczenie pojedynczej tabeli : jedna tabela dla wszystkich typów produktów z wystarczającą liczbą kolumn do przechowywania wszystkich atrybutów wszystkich typów. Oznacza to wiele kolumn, z których większość ma wartość NULL w dowolnym wierszu.

• Dziedziczenie tabeli klas : jedna tabela dla produktów, przechowująca atrybuty wspólne dla wszystkich typów produktów. Następnie jedna tabela dla każdego typu produktu, przechowująca atrybuty specyficzne dla tego typu produktu.

• Dziedziczenie tabeli konkretnej : brak tabeli dla typowych atrybutów produktów. Zamiast tego jedna tabela dla każdego typu produktu, przechowująca zarówno typowe atrybuty produktu, jak i atrybuty specyficzne dla produktu.

• Zserializowane LOB : jedna tabela dla produktów, przechowująca atrybuty wspólne dla wszystkich typów produktów. Jedna dodatkowa kolumna przechowuje BLOB częściowo ustrukturyzowanych danych w formacie XML, YAML, JSON lub jakimś innym. Ten BLOB umożliwia przechowywanie atrybutów specyficznych dla każdego typu produktu. Możesz użyć fantazyjnych wzorców projektowych, aby to opisać, takich jak Fasada i Memento. Ale bez względu na to, że masz zbiór atrybutów, których nie można łatwo przeszukiwać w SQL; musisz pobrać cały obiekt blob z powrotem do aplikacji i tam go uporządkować.

• Entity-Attribute-Value : jedna tabela dla produktów i jedna tabela przestawiająca atrybuty w wiersze zamiast w kolumnach. EAV nie jest prawidłowym projektem w odniesieniu do paradygmatu relacyjnego, ale i tak wiele osób go używa. To jest „wzorzec właściwości” wspomniany w innej odpowiedzi. Zobacz inne pytania z tagiem eav w StackOverflow, aby poznać niektóre pułapki.

Więcej o tym pisałem w prezentacji Extensible Data Modeling .

Dodatkowe przemyślenia na temat EAV: Chociaż wydaje się, że wiele osób preferuje EAV, ja nie. Wydaje się, że jest to najbardziej elastyczne rozwiązanie, a zatem najlepsze. Pamiętaj jednak o powiedzeniu TANSTAAFL . Oto kilka wad EAV:

• Nie ma możliwości, aby kolumna była obowiązkowa (odpowiednik NOT NULL).
• Brak możliwości użycia typów danych SQL do weryfikacji wpisów.
• Nie ma sposobu, aby zapewnić spójną pisownię nazw atrybutów.
• Brak możliwości umieszczenia klucza obcego na wartości dowolnego podanego atrybutu, np. W tabeli przeglądowej.
• Pobieranie wyników w konwencjonalnym układzie tabelarycznym jest złożone i kosztowne, ponieważ pobieranie atrybutów z wielu wierszy jest konieczne JOINdla każdego atrybutu.

Stopień elastyczności, jaki zapewnia EAV, wymaga poświęceń w innych obszarach, prawdopodobnie czyniąc kod tak złożonym (lub gorszym) niż rozwiązanie pierwotnego problemu w bardziej konwencjonalny sposób.

W większości przypadków taki stopień elastyczności nie jest konieczny. W pytaniu OP dotyczącym typów produktów o wiele łatwiej jest utworzyć tabelę według typu produktu dla atrybutów specyficznych dla produktu, więc masz pewną spójną strukturę wymuszoną przynajmniej dla wpisów tego samego typu produktu.

Użyłbym EAV tylko wtedy, gdyby każdy wiersz musiał mieć potencjalnie odrębny zestaw atrybutów. Kiedy masz ograniczony zestaw typów produktów, EAV jest przesadą. Dziedziczenie tabeli klas byłoby moim pierwszym wyborem.

Aktualizacja 2019: Im częściej widzę ludzi używających JSON jako rozwiązania problemu „wielu atrybutów niestandardowych”, tym mniej podoba mi się to rozwiązanie. Sprawia, że ​​zapytania są zbyt złożone, nawet jeśli do ich obsługi używane są specjalne funkcje JSON . Przechowywanie dokumentów JSON zajmuje znacznie więcej miejsca niż przechowywanie w zwykłych wierszach i kolumnach.

Zasadniczo żadne z tych rozwiązań nie jest łatwe ani wydajne w relacyjnej bazie danych. Cała idea posiadania „atrybutów zmiennych” jest zasadniczo sprzeczna z teorią relacji.

Sprowadza się to do tego, że musisz wybrać jedno z rozwiązań, na podstawie którego jest najmniej szkodliwe dla Twojej aplikacji. Dlatego przed wybraniem projektu bazy danych musisz wiedzieć, w jaki sposób będziesz wyszukiwać dane. Nie ma możliwości wybrania jednego rozwiązania, które jest „najlepsze”, ponieważ dowolne z rozwiązań może być najlepsze dla danej aplikacji.

@Serce z kamienia

Poszedłbym tutaj z EAV i MVC przez całą drogę.

@Bill Karvin

Oto kilka wad EAV:

• Nie ma możliwości, aby kolumna była obowiązkowa (odpowiednik NOT NULL).
• Brak możliwości użycia typów danych SQL do weryfikacji wpisów.
• Nie ma sposobu, aby zapewnić spójną pisownię nazw atrybutów.
• Brak możliwości umieszczenia klucza obcego na wartości dowolnego podanego atrybutu, np. W tabeli przeglądowej.

Wszystkie te rzeczy, o których tutaj wspomniałeś:

• walidacji danych
• sprawdzanie pisowni nazw atrybutów
• obowiązkowe kolumny / pola
• zajmowanie się niszczeniem atrybutów zależnych

moim zdaniem w ogóle nie należy do bazy danych, ponieważ żadna z baz danych nie jest w stanie obsłużyć tych interakcji i wymagań na odpowiednim poziomie, jak język programowania aplikacji.

Moim zdaniem używanie bazy danych w ten sposób jest jak wbijanie gwoździa kamieniem. Możesz to zrobić za pomocą skały, ale czy nie powinieneś używać młotka, który jest bardziej precyzyjny i specjalnie zaprojektowany do tego rodzaju czynności?

Pobieranie wyników w tradycyjnym układzie tabelarycznym jest złożone i kosztowne, ponieważ aby uzyskać atrybuty z wielu wierszy, należy wykonać JOIN dla każdego atrybutu.

Ten problem można rozwiązać, wykonując kilka zapytań dotyczących częściowych danych i przetwarzając je w układzie tabelarycznym w aplikacji. Nawet jeśli masz 600 GB danych produktów, możesz je przetwarzać partiami, jeśli potrzebujesz danych z każdego wiersza w tej tabeli.

Idąc dalej Jeśli chcesz poprawić wydajność zapytań, możesz wybrać określone operacje, takie jak np. Raportowanie lub globalne wyszukiwanie tekstu i przygotować dla nich indeksowe tabele, które będą przechowywać wymagane dane i będą okresowo odświeżane, powiedzmy co 30 minut.

Nie musisz nawet martwić się kosztem dodatkowego przechowywania danych, ponieważ każdego dnia staje się coraz tańszy.

Jeśli nadal interesuje Cię wydajność operacji wykonywanych przez aplikację, zawsze możesz użyć Erlang, C ++, Go Language do wstępnego przetworzenia danych, a później po prostu przetworzyć zoptymalizowane dane dalej w swojej głównej aplikacji.

Jeśli używam Class Table Inheritanceznaczenia:

jedna tabela dla produktów, przechowująca atrybuty wspólne dla wszystkich typów produktów. Następnie jedna tabela dla każdego typu produktu, przechowująca atrybuty specyficzne dla tego typu produktu. -Bill Karwin

Które z sugestii Billa Karwina podoba mi się najbardziej. Mogę przewidzieć jedną wadę, którą spróbuję wyjaśnić, jak nie stać się problemem.

Jaki plan awaryjny powinienem mieć na miejscu, gdy atrybut, który jest wspólny tylko dla 1 typu, następnie staje się wspólny dla 2, potem 3 itd.?

Na przykład: (to tylko przykład, a nie mój prawdziwy problem)

Jeśli sprzedajemy meble, możemy sprzedawać krzesła, lampy, sofy, telewizory itp. Typ telewizora może być jedynym typem, jaki posiadamy, który ma pobór mocy. Więc umieściłbym power_consumptionatrybut na tv_type_table. Ale potem zaczynamy oferować zestawy kina domowego, które również mają swoją power_consumptionwłaściwość. OK, to tylko jeden inny produkt, więc dodam to pole stereo_type_tablerównież, ponieważ w tym momencie jest to prawdopodobnie najłatwiejsze. Ale z biegiem czasu, gdy zaczynamy nosić coraz więcej elektroniki, zdajemy sobie sprawę, że power_consumptionjest na tyle szeroka, że ​​powinna znajdować się w main_product_table. Co mam teraz zrobić?

Dodaj pole do main_product_table. Napisz skrypt, który przejdzie przez elektronikę i umieść poprawną wartość z każdego type_tableelementu w pliku main_product_table. Następnie upuść tę kolumnę z każdego type_table.

Teraz, gdybym zawsze używał tej samej GetProductDataklasy do interakcji z bazą danych w celu pobrania informacji o produkcie; wtedy jeśli jakiekolwiek zmiany w kodzie wymagają teraz refaktoryzacji, powinny dotyczyć tylko tej klasy.

Możesz mieć tabelę produktów i oddzielną tabelę ProductAdditionInfo z 3 kolumnami: identyfikator produktu, nazwa dodatkowej informacji, wartość dodatkowej informacji. Jeśli kolor jest używany przez wiele, ale nie wszystkie rodzaje produktów, możesz ustawić go jako kolumnę dopuszczającą wartość null w tabeli Product lub po prostu umieścić go w ProductAdditionalInfo.

To podejście nie jest tradycyjną techniką dla relacyjnej bazy danych, ale widziałem, że jest często używane w praktyce. Może być elastyczny i mieć dobrą wydajność.

Steve Yegge nazywa to wzorcem Właściwości i napisał długi post o używaniu go.