Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns:
Date (DateTime), Value_of_rainfall (Float)
Each of these tables are for a specific location.
It has been suggested i create another table with the following columns:
LocationID (tinyInt), LocationName(char(6))
and insert into the first table a new column called LocationID(tinyInt).
Now the confusion i have is regarding that the data i have stored in the rainfall data datatables. Its been suggested that all the data from each location be contained in one data table. The statistical analysis im looking to carry out on the info is very (as far as i have envisioned thus far) location specific and wont require querying multiple locations at once. A couple months worth of data for one location = nearly 3 million rows and im looking to set up long running calculations on the data. therefore would a data warehouse be more appropriate? If so, could someone give me some pointers on how i should lay it out?
Thanks for your time.
Note: Im using Sql Server 2008