Advice about saving weekly data on MySql

Startbeitrag von StefanoG am 11.07.2015 15:41

How can I smartly save weekly data of a company, calculated from sale/buy/other movements?
It could be useful in order to avoid to recalc them any time a user wants data on a period.

Data means:
sales, purchases, unloads, givebacks, inventory rectifies and so on
each with quantity and $ values
with weekly/monthly granularity
for using on queries like "how much did I sell in the first 2 week of the last year?"

MySql cannot save array columns, you cannot specify a field with a subscript (to store arrays you have to serialize them and save them as is with conversion operations in read/write).
So the only DB design choice seems to be having a record for each week of the year..
You can have the company branch (if multi-branch), year, item in your record key but you need also the perido (week for example) inside the key. You cannot store all the data (sales, purchases, ecc) of ALL the weeks of a year in the same record.

Did I miss something?
Thanks

Antworten:

Hi Stefano,

you can certainly store precalculated stats in a record per week, if that is the smallest grain you will need, or you can even store them in a record per day, as I am doing for the new statistics function in wxreplication...

If you REALLY want to store ALL the stats of a year in ONE record (but I don't see why you would want to do that), you can of course do it by having a simple record with the period as key, and a text memo field with everything in it, by example using a NAME=Value pair structure. So you would have something like;
w1Sale=1523.23
w1Purchase=222.23
...
w23Sale=...

And so on

The advantage of that method (that you can use with one record per year, or per month or per week or even per day), is that any NEW statistic that you want to precalculate can be added simply by adding some login in the code, without changing the data structure.

If you are interested, I have some classes dealing with Name=Value pairs in a memo string available as part of WXReplication.

Best regards

von Fabrice Harari - am 11.07.2015 16:07
Tank you for the advice. I agree with you about the no real need of storing all the year in one record.
I was evaluating the optional only because the old erp did that and it was quite quick in retrieving the stats info

von StefanoG - am 15.07.2015 18:13
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.