MySQL for easy statistics

Question

I'm planing to automatizate process of statisitcs collecting at work, and decided to use MySQL to store data.

I'm working for tourist info-centre, so statistics (once a month) looks something like this:

Date:  1 2 3 4 5 6 7 8 9 10 ...
Museum  5 3 12 34 1 ...
Kremlin 5 1 43 12 9...

So the first column is the list of the objects (different types, by the way like sightseeings, hotels, restaurants, and so on) and the first row is the list of the days in month. On the crosses of them - amount of people who asked information about them.

So, what is the best, the right, the fastest way to do this in MySQL?

I mean how many tables should i create, and how (like a table per month, or the other way?) and how put information inside correctly, so it will be easy to check info like "How many people visited Kremlin from 1 of july to 25 of august?" or "How many people visited Museum totally in 2010?" and so on ,)

Thank you a lot in advance. And sorry for my english ,)

UPD: I'm planing to make a programm, so i can just press "Museum" button, and it will increase the number in particular date.

Answer 1

I would just log the visits with date and type in a raw table. And then you can get whatever statistics you need by SQL queries.

Answer 2

i think best solution is to have 2 tables 1. "sightseeings" (id, name) 2. main statistics table (id, sightseeings_id, date, ppl_count)

and after that you can write 1 SQL query for all... something like

SELECT SUM(ppl_count) FROM `main_statistics_table` WHERE date > 'from_date' AND date < 'to_date' AND sightseeing_id = 1

* main_statistics_table is the name of main statistics table statistics_id is id of row from statistics_table which is needed