I am keeping data in key/value pairs. I know how to convert that data into crosstab form using queries like this:
<!-- language: lang-sql --> SELECT master.tag, MAX(CASE WHEN data.key = 1 THEN data.value ELSE NULL END) AS F1, MAX(CASE WHEN data.key = 2 THEN data.value ELSE NULL END) AS F2, MAX(CASE WHEN data.key = 3 THEN data.value ELSE NULL END) AS F3 FROM master left join data on data.master_id= master.id GROUP BY master.id
In fact I am using more complicated version with dynamically created query, providing only fields which exist for selected master records.
This solution provides read-only recordset. Now, how to create a similar updatable recordset ?
I see three solutions, but none of these looks good:
1. Use joins like the code below:
SELECT master.tag, data1.value AS F1, data2.value AS F2, data3.value AS F3 FROM master inner join data as data_1 on data_1.master_id=master.id and data1.key=1 inner join data as data_2 on data_2.master_id=master.id and data2.key=2 inner join data as data_3 on data_3.master_id=master.id and data3.key=3
Performance is terrible at 10 joins and only a few master records.
2. Use view (read-only) to export current data along with data table id's and write own table editor.
This option is not practical. A form with custom update handling could be doable and useful but is not sufficient for me / does not cover all user tasks.
3. Create a temporary table with triggers in a SPROC. Edits would be made on this table and all changes would be forwarded to KVP storage by triggers.
This option looks the most practical, but there are issues with table lifetime. I have to use regular tables because true temporary tables can not have triggers. Each user may open more than one table. I have to find a way to detect which tables are no longer needed.
The question is are there any better options than the 3 above? If not, how to implement option 3 properly?
Bakground: The backend is MySQL, the frontend is MS Access 2007. Data is very irregular. There will be 60-150 various keys in KVP table but only 15-30 will be used for a given master row. Data can be grouped in classes and each class has specific set of keys. There can be 1-30 instances of each class. The idea is to let the user work like it was a regular table but limiting rows to one or a few classes and showing only columns (keys) which exist in all classes/rows.