Zapytanie dla statystyk Opcje

[piotr485]

Witam, przechowuje statystyki w bazie danych i mam teraz tabelę, ale wydaje mi się, że nie jest ona zbyt dobrze skonstruowana.

Mianowicie mam katalog firm, galerie zdjęć i wiadomości w kilku miastach

Chciałbym prowadzić statystyki dzienne dla:
1 - wyświetleń kategorii
2 - wyświetleń podkategorii
3 - wyświetleń firmy
4 - wyświetleń zdjęcia

dlatego postanowiłem stworzyć taką tabelę:

id|id_obcy|id_rodzaj|id_miasta|data|licznik

gdzie id_rodzaj to te od 1 do 4 pkt co napisałem. Każdy rodzaj jest trzymany w innej tabeli dlatego dałem id_obcy tylko on tak mnie męczy i tak się zastanawiam czy da struktura tabeli jest prawidłowa ?

Indeks nałożyłem na datę bo to po niej wyciągam np. liczbę wyświetleń firmy dla każdego dnia (z ost. 30 dni) i klucz obcy na id_miasta

i tak się zastanawiam czy w ogóle potrzebne mi tutaj pole id bo i tak go nigdzie nie używam przy odczytywaniu wyników ani dodawaniu ?!

Ktoś naprowadzi na właściwą i optymalną drogę ? (IMG:style_emoticons/default/smile.gif)

[trueblue]

Klucz obcy (id_obcy) na nic nie przyda (i obyś nie założył więzów integralności z kluczami głównym tych tabel) oprócz walorów informacyjnych.
Jeśli nie będziesz tam przechowywał danych zbiorczych, to tym bardziej tabela nie ma sensu, w takim układzie lepiej przechowywać liczbę wyświetleń bezpośrednio przy rekordzie w głównej tabeli.
Chyba, że zbiorczo, wtedy na 100% id_obcy nie jest do niczego potrzebny, wtedy wystarczą 4 pozostałe kolumny. Id może się przydać na cele ręcznego usuwania.

[piotr485]

nie mogę przechowywać liczby odwiedzin w tabelach głównych czyli kategorii, podkategorii, firmach i zdjęciach ponieważ chcę mieć możliwość wyświetleń statystyk z konkretnego dnia w konkretnym mieście.

nie bardzo rozumiem, że id_obcy mi się nie przyda przecież to jest najważniejsze pole w tej tabeli ponieważ tam są trzymane kategoria.id, podkategoria.id, firma.id lub zdjecie.id i po tym id się odwołuje później żeby wyświetlić statystyki

Ten post edytował piotr485 17.08.2016, 20:18:12

[trueblue]

nie bardzo rozumiem, że id_obcy mi się nie przyda przecież to jest najważniejsze pole w tej tabeli ponieważ tam są trzymane kategoria.id, podkategoria.id, firma.id lub zdjecie.id i po tym id się odwołuje później żeby wyświetlić statystyki

Napisałem, że ma tylko walor informacyjny. Trudno go uznać za klucz obcy, bo nie odwzorowuje bezpośrednio klucza w głównej tabeli (możesz mieć w tabeli statystyk dwie wartości id_obcy=2 i nie będziesz wiedział, który rekord wskazuje, dopiero razem z id_rodzaj).


Po co Ci id kategorii, podkategorii, itd. skoro wobec poniższego zbierasz tylko statystyki "z konkretnego dnia w konkretnym mieście"?

nie mogę przechowywać liczby odwiedzin w tabelach głównych czyli kategorii, podkategorii, firmach i zdjęciach ponieważ chcę mieć możliwość wyświetleń statystyk z konkretnego dnia w konkretnym mieście.

W Twoim przypadku, chyba jednak zdecydowałbym się na rozbicie tego na odrębne tabele.
Zastanów się jak będziesz wyciągał dane. Zapewne 4-ma odrębnymi zapytaniami (WHERE id_rodzaj=1, WHERE id_rodzaj=2, itd.). A teraz jakbyś wyciągał dane gdybyś miał statystyki w odrębnych tabelach? Nie uważasz, że delikatnie skomplikowałeś sobie rozwiązanie?

Ten post edytował trueblue 17.08.2016, 20:53:46

[piotr485]

Po co Ci id kategorii, podkategorii, itd. skoro wobec poniższego zbierasz tylko statystyki "z konkretnego dnia w konkretnym mieście"?

No dzięki temu mogę sprawdzić ilość wyświetleń dla konkretnej kategorii lub firmy czy zdjęcia dla konkretnych dni czy miesięcy dla konkretnego miasta

id_rodzaj będę miał więcej niż 4 dlatego postanowiłem trzymać wszystko w jednej tabeli, a jeśli chodzi o wyświetlanie danych to warunek będzie:
id_rodzaj = $id_rodzaj AND id_obcy = $id_obcy AND data = (miesiąc albo dzień) AND id_miasta = $id_miasta

W Twoim przypadku, chyba jednak zdecydowałbym się na rozbicie tego na odrębne tabele.
Zastanów się jak będziesz wyciągał dane. Zapewne 4-ma odrębnymi zapytaniami (WHERE id_rodzaj=1, WHERE id_rodzaj=2, itd.). A teraz jakbyś wyciągał dane gdybyś miał statystyki w odrębnych tabelach? Nie uważasz, że delikatnie skomplikowałeś sobie rozwiązanie?

Masz na myśli stworzenie tabeli statystyki_kategorie, statystyki_podkategorie, statystyki_firmy itd ? dzięki temu pozbędziemy się tego jednego pola id_rodzaj ? co w tym jest lepszego ?

[trueblue]

No dzięki temu mogę sprawdzić ilość wyświetleń dla konkretnej kategorii lub firmy czy zdjęcia dla konkretnych dni czy miesięcy dla konkretnego miasta

Tak, ale to nie wynikało z Twojego określenia zakresu statystyk.

Masz na myśli stworzenie tabeli statystyki_kategorie, statystyki_podkategorie, statystyki_firmy itd ? dzięki temu pozbędziemy się tego jednego pola id_rodzaj ? co w tym jest lepszego ?

Tak.
W obydwu przypadkach i tak będziesz musiał wykonać tyle samo zapytań, ale nie musisz pamiętać jaki id_rodzaj jest dla każdej tabeli (mechanizm bazy danych samoczynnie nie zapewni wyboru odpowiedniej tabeli na podstawie tego pola). Tym bardziej, że wspominasz, że będzie więcej rodzajów/tabel...

[Pyton_000]

Ja użyłem u siebie zbiorczej tabeli statystyk. Może to Ci pomoże:

Kod

CREATE TABLE `statistics` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `pageable_id` INT(10) UNSIGNED NOT NULL,
    `pageable_type` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `ip` VARCHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
    `user_agent` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `bot` TINYINT(1) NOT NULL,
    `referrer` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB

INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (1, 'App\\Models\\Category', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:28', '2015-12-30 22:36:28', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (4, 'App\\Models\\Category', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:30', '2015-12-30 22:36:30', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (3, 'App\\Models\\Category', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:32', '2015-12-30 22:36:32', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (11, 'App\\Models\\Category', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:33', '2015-12-30 22:36:33', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (46, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:35', '2015-12-30 22:36:35', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (43, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:40', '2015-12-30 22:36:40', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (58, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:43', '2015-12-30 22:36:43', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (44, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:36:59', '2015-12-30 22:36:59', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (47, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:00', '2015-12-30 22:37:00', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (12, 'App\\Models\\Post', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:03', '2015-12-30 22:37:03', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (66, 'App\\Models\\Tag', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:05', '2015-12-30 22:37:05', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (64, 'App\\Models\\Tag', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:07', '2015-12-30 22:37:07', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (25, 'App\\Models\\Tag', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:08', '2015-12-30 22:37:08', 1, NULL);
INSERT INTO `statistics` (`pageable_id`, `pageable_type`, `ip`, `user_agent`, `created_at`, `updated_at`, `bot`, `referrer`) VALUES (13, 'App\\Models\\Tag', '149.202.49.56', 'Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)', '2015-12-30 22:37:10', '2015-12-30 22:37:10', 1, NULL);

pagable_id - ID rekordu z tabeli której dotyczy odwiedzenie
pagable_type - typ np. tag, post, kategoria

Ten post edytował Pyton_000 18.08.2016, 09:54:48

[piotr485]

Okej dzięki za pomysły i sugestie, postanowiłem zrobić osobne tabele (IMG:style_emoticons/default/smile.gif)

[Pyton_000]

Osobnymi tabelami zrobisz sobie kuku. Raz że robisz duplikację, to i później trudno to utrzymać.

Mając jedną tabelę możesz sobie np. zrobić na niej partycje odpowiadające kategoriom, dzięki temu masz logicznie podzieloną tabelę i będą się zachowywały jak kilka mniejszych.
dobrze zaindeksowana tabela nie będzie odbiegała wydajnością od kilku mniejszych.