CODEWITHSUNDEEP
databasenosql
GorillaApe
GorillaApe

Reputation: 3641

What database to use for statistic / report data?

My application is a kind of POS system. The problem is with reports. Sales per product, per table, per staff,categories. Having 1 year date range reports are very slow because they have to sum lots of rows etc. So i am wondering if a no-sql database could help ,like having summaries per day or something.. But maybe its not easy because there could be for each item * products * categories * staff etc query. SO what could i do ?

Upvotes: 2

Views: 554

Answers (1)

dangowans
dangowans

Reputation: 2413

If you're comfortable with relational databases, I'd recommend sticking with them, and using daily aggregate tables for the reports you commonly use.

For example, if like to do sales reports grouped by product numbers, figure out what stats you're looking for (ie. quantity sold) and aggregate your raw data into day sized "buckets" by product number.

+-----------+------------+------------+-------+-------+
| salesdate | productNum | totalSales | stat2 | stat3 |
+-----------+------------+------------+-------+-------+

If you do day-sized buckets at the end of every day, you will only have 30 buckets per month for your report, or 365 buckets per year. Much faster to summarize. I've done this with network performance metrics when building out dashboards (hour-sized buckets), and it greatly reduces query time. You can always dig into the raw data if need be, but for the average user who wants to see something at a glance, the aggregated buckets are enough.

You may also consider putting the summary tables in a separate database.

Just keep in mind, if one of your stats in an average, the average of a series of averages is not the average for the overall range.

Upvotes: 5

Related Questions