[MySQL] Statistieken Structuur

[Ramasha]

Beste tweakers,

Ik ben bezig met een statistieken systeem, nu heb ik mezelf de volgende eisen gesteld welke uit de database moeten worden uitgelezen:

Opties:

# Per dag/Maand
# Unieke bezoekers
# Totaal aantal views
# Refferer's
# Zoekmachine bot's/crawlers

Veel meer informatie wil ik op dit moment niet opslaan. Voor deze informatie heb ik de volgende tabbellen:

code:

Stats_unique
+----+---------------------+
| id | IP                  |
+----+---------------------+
| 1  | 84.104.141.94       |
+----+---------------------+
| 51 | 81.68.193.58        |
+----+---------------------+
| 52 | 840.104.141.950     |
+----+---------------------+

Stats_hits
+----+-------+---------------------+
| id | ip_id | times               |
+----+-------+---------------------+
| 50 |  1    | 2006-04-22 15:55:36 |
+----+-------+---------------------+
| 51 |  51   | 2006-04-22 15:56:12 |
+----+-------+---------------------+
| 52 |  1    | 2006-04-22 15:56:41 |
+----+-------+---------------------+

Stats_refferer
+----+-------+---------------------+
| id | ip_id | refferer            |
+----+-------+---------------------+
| 50 |  1    | -                   |
+----+-------+---------------------+
| 51 |  51   | tweakers.net        |
+----+-------+---------------------+
| 52 |  1    | google.nl           |
+----+-------+---------------------+

Stats_crawler
+----+-------+---------------------+
| id | ip_id | crawler             |
+----+-------+---------------------+
| 50 |  1    | -                   |
+----+-------+---------------------+
| 51 |  51   | googlebot           |
+----+-------+---------------------+
| 52 |  52   | msnbot              |
+----+-------+---------------------+

Stats_unique bevat het IP. Ip_id uit de navolgende tabellen is de link terug naar Stats_unique. Via de timestamp kan ik dus in de query uitmaken of ik ze van een bepaalde datum wil of van een bepaalde maand. Iedere maand worden de oude statistieken gedumpt naar een andere tabel/xml file om zo de SQL server minder tot last te zijn, zo kan ik dus ook diep terug in de tijd.
Nu wil ik graag van jullie weten of deze tabellen goed zijn, met oog op performance en de eisen die ik mezelf heb gesteld. Ik sta altijd open voor kritiek dus brand los.

Vriendelijke Groet,

[djc]

Ik zou in plaats van alleen een crawler in het algemeen de User Agent van al je bezoekers opnemen, zo kun je ook zien welke browsers het meest gebruikt worden op je site. Verder zou je erover kunnen nadenken de gegevens uit stats_crawlers en stats_referers in 1 tabel op te nemen. Een probleem met je setup is uiteindelijk dat stats_hits enorm groot gaat worden; wellicht kun je iets bedenken om het ding eens in de zoveel tijd leeg te gooien en alleen aggregate statistieken te bewaren.

[Ramasha]

Manuzhai schreef op dinsdag 23 mei 2006 @ 14:16:
Een probleem met je setup is uiteindelijk dat stats_hits enorm groot gaat worden; wellicht kun je iets bedenken om het ding eens in de zoveel tijd leeg te gooien en alleen aggregate statistieken te bewaren.

Dat weet ik, en inderdaad zit ik er ook aan te denken, alle gegevens eens in de 24uur verwerken wegschrijven naar XML. Database leeggooien en je kan weer verder. Maar er zijn nog weinig mensen met kritiek?

Ben zelf aan het proberen geslagen met de tabbellen die hierboven staan, maar loop tegen wat rare dingen op. Maar dat is meer voor programmeer gedeelte. Ik wacht eerst nog wat meningen af.

EDIT:
Mmm bedenk me net als ik het per 24 uur analyseer kan ik niet zien hoeveel unieke bezoekers ik daadwerkelijk heb, ik hoop dat iemand nog een leuke visie heeft op dit probleem. Of zeggen jullie gewoon laat die Hits tabel maar groeien en leeg hem 1x in de maand?

[ Voor 16% gewijzigd door Ramasha op 23-05-2006 15:09 ]

[DamadmOO]

Wil je de tijd echt in uren/minuten/seconden hebben of gaat het alleen om het aantal bezoekers per dag?

Als je alleen aantal bezoekers per dag wilt hebben dan zou ik ervoor kiezen om tabel voor datum te maken met een unieke key. En dan in je hoofdtabel gewoon ip + datum + aantal hits op te slaan. Het aantal unieke bezoekers per dag is dan dus gelijk aan het aantal rijen van de betreffende datum.

[Ramasha]

DamadmOO schreef op dinsdag 23 mei 2006 @ 15:28:
Wil je de tijd echt in uren/minuten/seconden hebben of gaat het alleen om het aantal bezoekers per dag?

Als je alleen aantal bezoekers per dag wilt hebben dan zou ik ervoor kiezen om tabel voor datum te maken met een unieke key. En dan in je hoofdtabel gewoon ip + datum + aantal hits op te slaan. Het aantal unieke bezoekers per dag is dan dus gelijk aan het aantal rijen van de betreffende datum.

Nee, ik wil het echt op het uur nauwkeurig kunnen zien. Dan kan ik ook zien wanneer de meeste bezoekers komen wat ook niet onbelangrijk.