Best way to structure data

Question

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.

Answer 1

Hard limits:

• JOINs, including implicit ones in Views: 61.
• Columns per table < 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.)