Witam,

mam pewien problem związany z optymalizacją struktury bazy danych. Postaram się dokładnie to wytłumaczyć.

Tworzę pewien projekt, gdzie będzie można dodawać różne wartości, jednak będą one odzworowane jako różny typ kolumn w bazie (np. int, double, varchar). Jak najlepiej zapisywać te różne wartości w bazie. W projekcie używam Propel 1.6. Chodzi o to, że mogę dodawać wartość typu 'int' lub 'varchar' itd, aby przeszukiwanie tabeli było wydajne.

W sumie mam dwa pomysły. Pierwszy to stworzenie tabeli "value", która będzie miała kolumny: "id", "value_int", "value_double", "value_varchar", itd - z odpowiednimi typami kolumn. W zależności od typu wartości, rekordy będą zapisywane z wartością w odpowiedniej kolumnie (w reszcie będzie NULL).

Drugie rozwiązanie to stworzenie osobnych tabel, np. "value_int", "value_varchar" itd. Tu by były kolumny: "id", "value", które odpowiadałyby odpowiednim typom dla "value" (czyli, np. int, varchar, itd).

Nie ukrywam, że nie mam przekonania do żadnego z powyższych rozwiązań, pierwotnie zastanawiałem się nad jedną tabelą "value", gdzie kolumna byłaby typu "text" - jednak to rozwiązanie byłoby chyba jeszcze gorsze.

Chciałbym poznać Wasze zdanie na ten temat, może coś innego byłoby lepsze. Z góry dzięki.

|| edit:
prośba o przeniesienie do działu "Bazy danych" - drobna pomyłka

Wszystko zależy od tego, czy i jak często będziesz wyszukiwał po tej kolumnie. Ja w swoim projekcie zastosowałem rozwiązanie z kolumną typu TEXT. Działa dobrze, tylko że na postgreSQL.

Właśnie chodzi o to, że będę często wyszukiwał. Ogólnie tabela z wartościami to wierzchołek całej góry. Wartości będą powiązanie z innymi tabelami, i np. dla konkretnego wiersza z innej tabeli będą przyporządkowane wartości.

Dla przykładu:

Mamy 3 tabele:

USER: [tabela z użytkownikami]
* id
* name

FIELD: [tabela z polami profilu - gdzie kolumna 'type' to typ pola, czyli np. int, czy varchar)
* id
* type
* name

VALUE:
* id
* user_id - (FK user.id)
* field_id - (FK field.id)
* value

Czyli w tabeli USER mamy w każdym wierszu użytkownika, a jego profil jest zapisany w tabeli VALUE. Tylko że każde z pól profilowych może mieć różny typ (kolumna 'type' w tabeli FIELD), i na podstawie tego chciałbym żeby ta wartość trafiała do odpowiedniej kolumny, o odpowiednim typie.

Ktoś zasugerował mi normalizację... nie znam dokładnie jej definicji, na razie czytam o tym.

|| edit

Co do pola TEXT, to był mój pierwszy pomysł, ale chciałbym nie wrzucać pola typu TINYINT(1), do dużego pola TEXT, bo przy ogromnej liczbie rekordów w tabeli VALUE, bedzie, w/g mnie, niepotrzebny narzut wydajnościowy. To co napisałem z użytkownikami, to tylko przykład, dużo bardziej rozbudowany jest. Ale koncepcja jest taka, żeby były wartości przypisane do pewnych atrybutów, i w zależności od różnych typów, lądowały do odpowiedniej kolumny.

Pomyślałem, że jak byłoby kilka tabel, które bym JOINował i wybierał sobie odpowiednią wartość z jednej kolumny, która nie jest NULLem, to by rozwiązało problem, tylko czy nie będzie to zbyt mało optymalne?

Robiłem kiedyś coś podobnego, źródła dla Kohany masz tutaj:
http://forum.webmastah.pl/topic/2285-k2-dict-dane-sownikowe/
W Twojej tabeli VALUE kolumna value jest typu TEXT żeby móc przyjąć dowolne wartości. Cały myk polega na tym, że na podstawie danych słownikowych w locie budujesz widok (lub tabelę, ale widok zawsze zawiera aktualne dane) na zasadzie N JOINów wszystkich kolumn zawartych w słowniku rzutowanych na konkretny typ danych (poprzez CAST()) determinowany przez typ pola (u Ciebie FIELD.type). W efekcie otrzymujesz dane jakby z normalnej tabeli. Możesz wyszukiwać, dołączać inne tabele czy słowniki itd. Dodatkowo masz transparentość danych tj. możesz wstawiać dane do słownika jakby były normalną tabelą. Potrzeba do tego nakładki wstawiającej dane do tabeli VALUE, ja skorzystałem z ORM, który mapuje mi nazwa kolumny = ID kolumny z tabeli FIELD). Wydajność raczej nie będzie powalająca, ale zależy do czego chcesz to wykorzystać.

Korzystam w projekcie z ORM (Propel), ale właśnie wydajność wydaje mi się sprawą kluczową. Bo dużo rekordów będzie w tej tabeli. Ogólnie chcę pobierać jakieś obiekty (dane) z bazy z przypisanymi do nich atrybutami i wartościami. Te wartości mogą być różne: varchar, int, float, double itp, czasem będzie trzeba posortować po odpowiednich kolumnach. Nie chciałbym wszystkiego wrzucać do pola "TEXT", bo może być potrzebne typu Varchar(255) lub Tinyint(1). Ogólnie zależy mi na wydajności, bo przecież mógłbym zrobić kolumnę VALUE o typie TEXT, i po kłopocie. Chciałbym, żeby było to jak najbardziej optymalne rozwiązanie, pod względem szybkości i wydajności. Bo gdy będą skomplikowane operacje, to rozbicie wartości na różne kolumny może nie przynieść spodziewanych rezultatów i mogę zostawić jak jest.

|| edit:

W sumie wcześniej nie korzystałem z funkcji CAST (i CONVERT). Z tego co widzę to mogę ją wykorzystać. Czyli np. zrobić pole typu TEXT i konwertować pola na odpowiednie, w zależności od potrzeb, tak?