|Just another Perl shrine|
Re^2: mapping complex structures to mysql tablesby Laurent_R (Abbot)
|on May 08, 2013 at 21:15 UTC||Need Help??|
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.