in reply to Re^2: mapping complex structures to mysql tables
in thread mapping complex structures to mysql tables
Years ago, I stumbled into a very similar (legacy...) “kitchen-sink experience”and the basic problem turned out to be that all of the settings were being stored in one XML record/file. The application would absolutely grind to a dead-stop. But the actual solution turned out to be fairly simple: carve off three of the top-level sections of the file (thus dividing the total into 200-odd subsections), and store each piece as an XML-formatted BLOB field keyed by a set of fields consisting of three top-level keys.
Intuitively, I feel that the best overall solution to a problem like this one is to store the data in a BLOB (that is, a VARCHAR field of arbitrary size) in a “well-known format” ... JSON if you know you’re among friends, otherwise XML. This compromise allows you to let the database handle storage, of all or a subset of the data, without condemning you to a hell of impossible-to-maintain code that tries to “wedge” arbitrary data into a rows-and-columns pure SQL format. This is an extremely well-known and widely-used approach.