Best way to structure data I have been playing around with grib2 data from the National Weather Service lately, and have been databasing some model forecast data.
The current design model I have is probably bad design, but I have 87 columns of data per model per forecast hour I want to save. The long range forecast models have 52 forecast hour intervals I'm working off of, but I could increase that later. I'm collecting from 3 models... so right now that means I have 87 columns, 1 table per model per forecast hour - in other words, somewhere around 80 tables of data with 87 columns each.
Already, this is causing problems, as MySQL apparently has a hard limit for how many table joins you can do and I have hit this a few times. Also, I'm starting to get PHP errors trying to execute multiple queries at once and then joining the results in PHP to one data set - too many buffered connections, etc. - I currently have PHP doing the queries looping through forecast hours to auto-generate the table joins... but I want a better way to do this. It's very hacky sloppy.
Would it make more sense to:

Save the current table structure? (~80 tables per location - 1 right now)

Create a table just for hour intervals? (This could still hit the table limit if I increase the hour frequency - but would be a bit less tables. Each table would have ~240 columns.)

Create a huge table with all hour data per each model. Would have 3 tables, but one of those tables would have 6,880 columns of data. I understand there is a hard limit of around 4,000 columns? This would not work, probably.
Hard limits:


JOINs, including implicit ones in Views: 61.  
Columns per table &lt; 1000 (1017 in 5.6.9, InnoDB); 2598(?) for MyISAM -- But don't use MyISAM.
others


But why do you need a lot of JOINs?  Please elaborate on the schema (SHOW CREATE TABLE, leaving out many of the columns) and SELECT (leaving out all but a couple of representative JOINs).

Do you actually do arithmetic or searching in MySQL on the 240 readings?  If not, simply pack them in a JSON string and let the app break them apart.  Further, compress the JSON string (in the client) and store in a BLOB.  (Smaller --> faster)  PHP would have no problem unpacking 4000 fields with json_decode().  And it could be in the desired structure (array / hash / nested arrays / etc)

The main thing is to structure the schema for what works well in the database, then leave the rest for the application.  The database is a repository; the app is the compute engine.

One table per hour or one table per location -- this smells bad.  One table per location per hour would be really bad.  Once you have thousands of tables in a database (or thousands of databases), the OS gets sluggish in accessing them.

So... 3 (or 1?) tables with


location_id (normalized, to save space)
hour (probably a DATETIME)
one (or a few or maybe 80) JSON strings each with 87(?) numbers, structure for the convenience of the app.


(Sorry, I got lost in the dimensions -- 3 somethings * 80 something else * 87 * 52 * ???)

Plus, PRIMARY KEY(location_id, hour) (or possibly in the opposite order.  And (probably) a plain INDEX with those columns in the opposite order.  Pick the order in the PK based on which order is more important for the SELECTs.  (Don't worry about INSERTs.)