Data Structure for storing dynamic data

Question

I want to store data which is dynamic in nature. The user can create forms for capturing data. The data can be stored in various formats as per the configuration. The major ones are a RDBMS and a XML file. XML file format is pretty easy to store dynamic data and load it back.

I am not able to devise a data structure for a RDBMS. I currently store data in a key-value format and do a PIVOT for fetching it. For fields which have multiple values I store them as CSV in the value column.

Is there a better way for storing such dynamic data which helps in performance and extensibility?

Answer 1

Without knowing more about your application it is hard to say.

You could save the data as XML in a BLOB in the database. That would mean all your data was (sorta) handled the same way (as XML).

The other approach would be to change your database structure to hold nested data (which appears to be your problem). So instead of a straight key-value table you might hace a table structure that could reference itself (e.g. parent - key - value) and have a header table to hold the top level keys.

The real question though is why you want to use a database to hold the data. It seems the real problem is trying to fit a round peg into a square hole (or vice versa).