How can I structure my tables to cope with this data ?

habbardone

Hi,
I am struggling with how I should store and process this data.

I get an update every day which contains about 20,000 rows of data.
There are some new rows, maybe 150 a week, and some of the existing rows data changes.

Approximately 97 % of all the data is the same each day with just 3% changing.

I am using Mysql database tables.
So I was thinking that the best way to store the data would be:

Base_Data_Table - 20,000 rows (static)
Structure:
id, title, desc, data1, data2, ... data18

Current_Data_Table - 20,000 rows ( replaced daily)
Structure:
id, title, desc, data1, data2, ... data18

Transaction_Data_Table - 60 rows per day ( grows daily )
Structure:
date, id, record, new_data

--- record is the record that is changed
--- data is the new data fro that record

Although the above is nice and efficient for data storage, it is going to be
a nightmare to extract the data.

Everyday I need to be able to display the current data
( no problem with the Current Data table) but I also want to be able to calculate:

For every row ( could be done dynamically when needed)
a 2-day average for 2 data points (data7 and data8) for today
a 2-day average for 2 data points (data7 and data8) for 7 days ago
a 2-day average for 2 data points (data7 and data8) for 30 days ago

and grab the last 120 days data points for two records (data8 and data9)
so that I can chart them.

The easiest way to extract the data would be to just write a new table
for each day, but that would mean duplicating 97% of data - big waste of disc space.

I think that there must be a good compromise - but I am not sure what to do.
This must be a fairly common situation with database updates.
What usually happens if you you want to track and chart historic data ?

Does anyone have any suggestions on how to organize the data.

PS
I am thinking that maybe I should have another table:
... maybe I should calculate and store averages and also the chart data.

Chart_Data_Table - another 20,000 rows
date, id,data8,data9,data7_ave, data8_ave

These are the most changeable data

What do you think ?

Thanks.

habbardone

Just did some checking.

My daily file takes up 2.4 Mb

That means a year is 876 Mb - isn't that quite a lot ?

I must admit that it would make the reporting and charting
much easier.

It just seems that with 95% of that data is redundant.
But maybe it doesn't matter - who cares ?

My server has over 200Gb and I've only used 17Gb.

BUT - doesn't a big file take a lot longer to search through?

Or maybe its quicker to serch one large table rather than do several searches on smaller tables ??

Any experience with this ?

Thanks

Kudose

I don't have a lot of experience with this, but I think you would want to create a summary table and perform the calculations from that, after making sure you have the right indexes and maybe even using a MERGE table type.

Also, a huge table will be slower to search than a small table, but proper indexes and query optimization should mitigate the majority of it.