Odzwyczajony od blogowania, zaabsorbowany przez niekorzystnego dla planet i blogosfery stanu rzeczy Social Media, postanowiłem wrzucić kolejny wpis, w podzięce za porady uzyskane z blogów kolegów z branży. Ostatnio moim zadaniem było zaprojektowanie struktury tabel bazy danych, która wraz z drzewem z zagłębieniem kategorii, ma przechowywać dane na temat cen za ogłoszenia publikowane w tych kategoriach.

Założenia wstępne wyglądały następująco:

1. Każda kategoria może mieć rodzica, ale nie musi.
   Jak komu wygodnie, postanowiłem wybrać rozwiązanie z polem category_parent int(11) UNSIGNED, domyślnie NULL (bez rodzica).
2. Kategorie mogą mieć ustaloną cenę za opublikowanie ogłoszenia, ale nie muszą.
   Dodałem pole category_data_price float(6,2) UNSIGNED, domyślnie NULL (niezdefiniowana).
3. Kategorie, które nie mają zdefiniowanej ceny, a mają rodzica, przejmują cenę rodzica w zagłębieniu do nieskończoności. W przypadku, kiedy cena jest niezdefiniowana również dla rodziców, cena stanowi NULL.

Potrzebne mi było zapytanie, które zwróci mi zwyczajnie listę wszystkich kategorii wraz z cenami, uwzględniając zagłębienia, w jakich dana kategoria się znajduje. Podejścia składowania cen są dwa:

1. Można zapisywać tylko cenę kategorii, która jest dla niej zdefiniowana, a przy wyciąganiu danych rekurencyjnie sprawdzać procedurą/funkcją cenę rodzica, jeżeli jest niezdefiniowana. I tak w kółko do przypadku, gdy któraś z kategorii będzie już miała zdefiniowaną cenę, bądź nie będzie się już gdzie zagłębić (kategoria nie ma rodzica).
2. Można zapisać dwie ceny dodając pole dodatkowe, które przy będzie przechowywało wartość ceny, biorąc ją przy sprawdzaniu cen tylko przy zapisie rekordu do bazy danych.

Sposób drugi wydaje się być bardziej rozsądny w przypadku większej ilości zapytań podejmujących dane z bazy. Pierwszy odwrotnie – częściej wpisujemy coś do bazy. Z początku wybrałem pierwszy sposób, przy założeniu, że ceny będą generowane tylko w panelu administracyjnym, zatem zapytanie nie będzie wykonywane często. Niestety, moje myślenie jest bardziej abstrakcyjne, nastawione na elastyczność rozwiązań, w przyszłości cel istnienia bazy może się zmienić.

Tak czy siak, dla obu rozwiązań, trzeba stworzyć procedurę sprawdzającą ceny po rodzicach. Wygląda mniej więcej tak:

CREATE FUNCTION AnnouncementsCategoriesGetPrice(iCategoryId int(11)) RETURNS float(6,2)
BEGIN
DECLARE iResult FLOAT(6,2);
DECLARE iPointer INT(11);

SET iPointer = iCategoryId;
SET iResult  = (SELECT category_data_price FROM cms_announcements_categories WHERE category_id = iPointer);

WHILE (iResult IS NULL AND iPointer IS NOT NULL) DO
SET iResult  = (SELECT category_data_price FROM cms_announcements_categories WHERE category_id = iPointer);
SET iPointer = (SELECT category_parent FROM cms_announcements_categories WHERE category_id = iPointer);
END WHILE;

RETURN iResult;
END

Stosować ją można dla SELECT’u kategorii lub do pozyskania wartości pola tymczasowego przy zapisie do bazy.

Mam nadzieję, że komuś się przyda.