Table normalization for a custom Google Analytic alike system
Posted on 2014-09-10
I'm asked to create a Google Analytics alike application (of course just a simple one) for our organization so as you may know first thing which crosses my mind was thinking of IIS log file and use LogParser to extract information so this is my plan and I will appreciate it if you give me some heads up as far as the kind of tables I would need to have.
Currently I am think of a set of tablets as following:
0) All the information are stored into a table as RawInfo
This raw table gets following column/info from log file:
Id (incremental), date, time, c-ip, cs-username, s-sitename, s-ip, s-port, cs-method, cs-uri-stem, cs-uri-query, cs-status,
sc-substatus, sc-win32-status, sc-bytes, cs-bytes, time-taken, cs-version, cs-host, cs-UserAgent, cs-cookie, cs-Referer
1) From RawInfo we split the information in 8 tables base on the need we have.
id, date, time, c-ip, cs-username, s-sitename, method-id, status, win32status, time-taken, cs-version, cs-host
method-id, method-name e.g. GET/SET
id, date, time, c-ip, cs-uri-stem
id, date, time, c-ip, cs-uri-query
e.g. 200 = OK, 404 = page not found and etc
id, date, time, c-ip,cs-UserAgent
id, date, time, c-ip,cs-cookes
id, date, time, c-ip, cs-Referer
2) I'm thinking of using a combinational Primary key of id, date, time, c-ip
May some experts discuss/comment on this approach ?