Re: need help with tables/fields layout.

[Rick James]

I would not want to have more than, say, 10K tables. Various limits (including OS limits) might be hit.

The one-table approach could need messy self-joins to extract data.

Ponder this approach...
1. What will your SELECTs look like?
2. What indexes would be needed to support them?
3. What is the minimal set of table(s) and index(es) to SELECT just the indexed fields?
4. Which table(s) should get the rest of the 'columns'. I'm guessing (but can't really tell) that this last step is where your 'variable column' structure gets messy. Well, don't put them in columns. Instead,
5. Put the rest of the fields into a PHP associative array, then use json_encode on the array, and toss it into a single TEXT field that lives in the table(s).

Confused? Well, at least tell us about step 1.