I'm developing a traceability platform for the agro industry(a web and Android application) where the user can define a series of processes where the raw material becomes a final product. Each process generates data that must be captured (using the Android app). Because it isn't oriented to a specific kind of crop, the data to register will be different for each client.
Because I'm aware of the cons of EAV (entity attribute value model ),being the difficulty of developing the queries what concerns me the most, I'm evaluating these other options:
- XML data type in SQL Server: I would store the user defined data in a xml type column. It gives me the possibility of querying it within the SQL sentence.
- PostgreSQL Hstore: I can store the data in a key-value pair structure, with the possibility of querying it with a SQL sentence also.
- Once the user defined the data to collect in each process, the structure will not be modified too often, I would say once at most,I could create the DDL (create,alter,drop table ...) sentences based on the user input and I could develop a module to create the reports and queries for the user.
I don't have experience using XML data type column and Hstore in a project, what of these 3 options, would be the best suitable for my case?