0

I have a database that stores unordered user-collected data in the following format:

uID     format     value
ash001  height     37
ash001  disease    green
ash002  disease    red
ash003  disease    orange
ash003  height     39

When exporting, users prefer the following data format:

uID     height    disease
ash001  37        green
ash002            red
ash003  39        orange

Exporting the values from the database format to a CSV file is relatively simple and causes no issues. When I try to export the user-preferred format, I quickly run into performance issues. My current approach for organizing and exporting is to get a list of the different 'formats' and 'uIDs' and then perform single queries to obtain each 'value' to fill the table. This leads to many queries (often with empty values) and very sluggish performance. The max uIDs and formats I've observed was 9000x20.

I'm interested in finding a better approach that would speed up the export process.

trvrr
  • 25
  • 5
  • I think the easiest solution would be to copy the data into a new temporary table that is in the format you want to export and then export it. Or just store the data in the in the format you want to export it in the first place. – DrkStr Sep 15 '14 at 16:40
  • Your db is **poorly designed**. You should adopt the customer suggested format, instead. – Phantômaxx Sep 15 '14 at 16:49
  • Frank, the problem is that the customer can redefine additional formats at any time in the application which would require the data table to be redesigned with each additional format addition or deletion. – trvrr Sep 15 '14 at 16:56

1 Answers1

0

Other questions on Stack Overflow led me to find that this model is called the entry-attribute-value (EAV) model and is very much not-preferred for data storage. Unfortunately, the format of our data and additional metatdata collected requires us to use this format. To convert this into a true relational format, I used the multi-join query provided in this answer: What is best performance for Retrieving MySQL EAV results as Relational Table

Community
  • 1
  • 1
trvrr
  • 25
  • 5