For my bachelor thesis I have to build a reseach database which has to meet the requirement of provenance. In the present case it means that among other things I have to store the results of my queries and the query itself in the database.

The problem I encounter is the fact that I can get results which structure is not known before. I can get scalar result, e.g. an average value, I can get an n-tuple as a result or even tables as a result of a table function. The user is allowed to perform any operation on the raw data and the result has to be stored in the database.

My thesis tutor suggested me to use the EAV schema. I have read a couple of posts on DBA and have seen that this can get extremely complicated, especially when you have something like nested datatypes in the result (e.g. A graph as a result has points, a point has X- and Y- coordinates, the coordinates have float-values. I implemented an EAV schema in MySQL and have seen the complexity to store this). Nearly everyone disadvised to use EAV on DBA.

Is there a useful alternative to this EAV schema in my case? I have read some papers about some approaches with NoSQL databases but the problem is that I have to use MySQL because the institution which takes care on my thesis uses MySQL for their curated research databases.

  • It may be stored in the database the same way as files/images can be. It is not the right thing to do in most cases (imho) but is possible. Just store it as a blob (serialized or something). But that may not be possible/effective if you need to somehow "work" with the results in SQL - do you need just to find and load it by some id/tags etc. (ok) or search by some parts of the results too? (that won't be easy with the eav OR the blob, but might be a bit simpler with eav). – jkavalik Nov 20 '15 at 7:24
  • 1
    "Storing data with an unknown structure" has a multitiude of implications, easily requiring the effort of a bachelor thesis. Most of the arguments against EAV are given in cases where the structure of the data is known, at an intuitive level, by the subject matter experts, but unknown to the would be database architect. Discovering and formalizing the data structure is just too much work, so they resort to EAV as a way of shortening the development curve. Your case is entirely different. The arguments that people like me make against EAV do not apply to your case. – Walter Mitty Nov 20 '15 at 11:31
  • Postgres + hstore (or JSON) is the best relational NoSQL solution you can get - much better than any EAV can ever be. – a_horse_with_no_name Nov 20 '15 at 14:14
  • EAV gets in trouble when you need to search for "product='dress' AND color='green' AND size=14". You may not need any of that. Simply have a table with two MEDIUMINTs -- the query and the results. (Plus timestamp, id, etc.) More on EAV. – Rick James Dec 3 '15 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.