Dear all,

I have a requirement to design table which should support multiple fields. The number of fields are not fixed as in the normal case. The user is given an option to choose the data that he needs which keep varying based on templates.

I have identified various solutions and would like to know how will the performance be while using hibernate as the ORM. Looking for suggestions on the choice of the solution.

solution 1)
Table designed with fixed columns (maximum possible columns ) say textfield1,2 ..10, datefield1,2,..10 etc. Here not always all the columns will hold data. Based on the users choice only subset of these columns will hold data. But a single object will hold the complete record


solution 2)

Table designed with Key ,value pair. Ie. Seperate tables to hold date type data, varchar, numeric etc. Each table will hold the ID,fieldname,value. In this approach Numerous number of fields can be allowed since as many rows can be inserted into the tables. And also the data type is maintained without the drawback of storing all data as varchar and then converting as separate tables being used.

But in this approach I would need to access multiple tables, multiple rows to get my complete record. But my concern is about the performance. I also need to allow search facilities.

Looking for suggestions,

Patel.