Re: What's the worst that can happen with below database design? Immediately or in the future?

[Rick James]

> ALL the data which is stored in the database are stored in their own fields properly according to their types.

And I am suggesting that, except for fields that need to be indexed, then can very easily be thrown into a single JSON field. Since you already have the PHP framework, json_encode() and json_decode(,TRUE) work very nicely.

As for "according to their types", that is mostly irrelevant. "123" and 123 and 123.0 are treated identically in PHP, so MySQL's distinction between VARCHAR, LONGTEXT, INT, MEDIUMINT, DECIMAL, FLOAT, and DOUBLE is moot. JSON is a reasonable go-between. See also YAML as an option. I do not recommend XML; it does not map nicely to/from PHP.

Dates are trickier; the "right answer" is to conform to something standard ("yyyy-mm-dd hh:mm:ss" or ANSI, which is slightly different), and then provide the suitable conversion whenever needed. Again, PHP is probably a better vehicle for converting; JSON does not care.

If the types matter, then it is probably best to do the type-checking in PHP, not depend on MySQL.

Another "model" for something like what you describe is RDF. (This is actually where I learned how unscalable EAV could be.)

At 10K rows, your data (and indexes, if any) can probably fit in RAM. This would lead to very little I/O, and possibly not too much CPU. So, in a way, my comments about scalability are irrelevant to you.

Still, JSON is so much easier to deal with than futzing with field1, field2, etc, and sticking short strings in LONGTEXT, etc, that if I were embarking on your task, well, you can guess which way I would go.