I have the web application where i am serving file downloads in php.All is working fine and i have the download column in file field where i increment it.But i want to have full statictics of downloads like how many downloads on each day or in month or time of day or from which locationBut i am not able o find how to design the mysql tables for that. Do i need to lo every download request in database for that. Any design pattern or example of that in real world
how do design the database tables for file download statistics
programming languages;php;database;mysql
Ordinary, you'll save the date and time and location of each download, given that the table should be optimized for fast insertion, but retrieval may be slow.Then, you may have an OLAP cube which, based on the original table, will store the number of downloads in a way appropriate for your criteria, i.e. per day or month or location.You can start by modeling the table which is used to record download hits:This gives you basic information about the download, i.e. that a download happened at a given date and time from a given geographical location. isProcessed is 0 by default; you'll see how is it used a bit later.Given the lack of indexes on the table, insertion of data would be fast. Selection, on the other hand, can be really slow, but it doesn't matter: we wouldn't use this table for search.The data from this table would be transformed into this one on schedule (at night, every hour, once per second, depending on your requirements). Once transferred, the entry is marked as processed by changing IsProcessed to 1.This table contains indexes to be able to quickly find the information you need, for example count downloads per location like this:select sum(CountDownloads) from ProcessedDownload where GeographicalLocation = :location`It may be enough for your needs, or you can go further and do an OLAP cube with specific dimensions depending on the criteria you need.If you want to go even further, search about snowflake schema. You can implement it for both date-time and location, given that depending on the context, the star schema (the current one) may be more appropriate.As suggested in the comments, server logs may be another source for the ETL, replacing the database table. I imagine the pros would be:No database to create in the first place; no connections to the database to manage, etc.The previous point also means that you don't have to do anything with your web application to be able to report such statistics. Expanding statistics to other web applications is straightforward too, since you don't need to change them.Server logs are expected to be very fast, very probably faster than the insertion of a row in a table in a database.