Re: mapping complex structures to mysql tables

[jnyman]

Have you considered of storing the configuration variables as XML in the database? There also seems to be a mysql function called ExtractValue which you can use to select data with XPath.

[Laurent_R]

Have you considered of storing the configuration variables as XML in the database?

Hmm, personally, I would be quite reluctant to this type of solution, because, in my experience, it is not very scalable.

The very large company for which I work right now as a freelance consultant is using a large billing application running under Oracle. For various reasons which I ignore, it was decided years ago to store some configuration items as XML in a few database fields. No problem in principle, since this is relatively static data which can be loaded into memory every time the application is started.

Except that these configuration XML objects grew over time. One of them is now about 75 MB and another one 30 or 40 MB, I don't remember exactly its size. This has become pretty much unmanageable for a number of purposes. For example, it has become practically impossible to just consult these with the available GUIs, SQL browsers and other tools available. It is not even possible to display the data and copy and paste it in a text editor or an XML browser.

So, this idea of storing reference data into an XML object might have looked as a neat trick at the time the decision was taken, today, it has become a nightmare.

In brief, the idea of storing reference data in an XML object within the database does not look right to me. Too many problems.

My first idea for this problem would be that what is needed is some tool to parse the data structure (the HoH, HoA, etc.), or the files from where they are loaded, and translate that into an SQL data dictionary (I mean an SQL tables data structure reflecting somehow the current data structure). Once this is done, it should not be too difficult to load the data into a MySQL or other database. I do not know of any module to do that straight from the box, but combining existing CPAN modules might lead to a relatively light solution.

An possible alternative solution would be a NoSQL database, together with one of the NoSQL DBI modules. The advantage is that a NoSQL data model (for what I know about it, i.e. quite little) has a structure usually more closely related with hashes and HoHs and the like (or JSON) than the SQL model, so that the translation from one to the other might be much easier.

[sundialsvc4]

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.

[jnyman]

Yes, i can agree that XML is more like an easy fix and not scalable in that sense.