On a stats-system, should I save little bits of information about single visit on many tables or just one table?

Question

Hello

I've been wondering this for a while already. The title stands for my question. What do you prefer?

I made a pic to make my question clearer.

Why am I even thinking of this? Isn't one table the most obvious option? Well, kind of. It's the simpliest way, but let's think more practical. When there is a ton of data in one table and user wants to only see statistics about browsers the visitors use, this may not be as successful. Taking browser-data out of one table is naturally better.

Multiple tables has disadvantages too. Writing data takes more time and resources. With one table there's only one mysql-query needed.

Anyway, I figured out a solution, which I think makes sense. Data is written to some kind of temporary table. All of those lines will be exported to multiple tables later (scheduled script). This way the system doesn't take loading-time from the users page, but the data remains fast to browse.

Let's bring some discussion here. Hopefully Stackoverflow staff doesn't delete my question, as there may not be only one right answer. I'm hoping to raise some opinions.

Which one is better? Let's find out!

Martti Laine

Answer 1

The date, browser and OS are all related on a one-to-one basis... Without more information to require distinguishing records further, I'd be creating a single table rather than two.

Database design is based on creating tables that reflect entities, and I don't see two distinct entities in the example provided. Consider using views to serve data without duplicating the data in the database; a centralized copy of the data makes managing the data much easier...

Answer 2

What you're really thinking of is whether to denormalize the table or use the first normal form. When you're using 1NF you have a table that looks like this:

Table statistic
id     | date       | browser_id      | os_id
---------------------------------------------
1      | 127003727  | 1               | 1
2      | 127391662  | 2               | 2
3      | 127912683  | 3               | 2

And then to explain what browser and os the client used, you need other tables:

Table browser
id      | name        | company      | version
-----------------------------------------------
1       | Firefox     | Mozilla      | 3.6.8
2       | Safari      | Apple        | 4.0
3       | Firefox     | Mozilla      | 3.5.1

Table os
id      | name        | company      | version
-----------------------------------------------
1       | Ubuntu      | Canonical    | 10.04
2       | Windows     | Microsoft    | 7
3       | Windows     | Microsoft    | 3.11

As OMG Ponies already pointed out, this isn't a good example to be creating several entities, so one can safely go with one table and then think about how he/she is going to deal with having to, say, find all the entries with a matching browser name.