Single table database with multiple dates:how to redesign

[wthrman]

I have a large, single table database that keeps track of daily rainfalls from over 7000 locations around the country. This means that for every day, the year,month,day and station name are repeated. The database gets bigger each day and performance is being badly affected more and more (not surprising). I do a lot of SQL querying with the data. I know I could archive some of the older records but really that solves nothing in the long run. The table presently has over 3.5 million records and takes nearly half a minute to open. Compressing the database makes no difference.

The problem relates to efficient design. Although I've read up a fair bit on database design I really don't know how to sort it out. I'm really scarcely a novice at this.

Would there be any advantage in keeping seperate tables for unique days, months, years and station names, as well as the original table of data (downloaded daily so I have no control over its initial format)? If not, could someone please suggest an alternative especially so that tables and queries would run faster?

Thank you

 

[SkipVought]

hi,

This means that for every day, the year,month,day and station name are repeated.

Do you mean that the table structure changes (columns are added) or that only rows are added?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

It sounds to me like you're saying it takes at least 7,000 records in every day. There are other ways you could design it, but if your table structure is something like:

Date/Time, Location, Temperature

Then there isn't much else to do. If you create a date table, you'll still have the same number of records, b/c each instance of the temperature at each location on each date IS a unique record.

If you want to consider something else, then why not try out a SQL database. Either MySQL or Oracle or MS SQL Server. Really, that's the route you're going to have to go to be able to handle that much data. At some point, Access will just go tilt... unless you setup an automatic archiving or splitting system, which will eventually get very complex for a maybe 4 field database table.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

You might want to post in forum669.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wthrman]

Thanks guys for the responses. Skip, the data are added in rows, the columns remain the same.

I had wondered whether indeed I was going to have to go from Access to something else, so I'll look into the alternatives.