Assume I have a DB model that supports Entity-Attribute Values. If I had two records that each had multiple EAV entries, for example:
Apple
Car
I could make a CSV for apple easy:
product, size, color
apple, small, green
apple, small, red
apple, large, green
apple, large, red
I could do the same for car, too.
The problem is what if I wanted apple and car to be in the same CSV? Should my CSV headings look like this:
product, size, color, type, fuel
This doesn't make sense, though. Apples don't have a car type or fuel. And cars don't have a size and color (based on the examples I gave).
Should I employ something like this:
product
apple, size:small, color:green
apple, size:small, color:red
apple, size:large, color:green
apple, size:large, color:red
car, type:coupe, fuel:diesel
car, type:coupe, fuel:gas
car, type:sedan, fuel:diesel
car, type:sedan, fuel:gas
The consumer of the CSV will just need to know that after the first column are the name-value pairs representing the EAV.
What's a good approach? Suggestions?
This is the solution I am going to implement:
product, name_1, value_1, name_2, value_2
apple, size, small, color, green
apple, size, small, color, red
apple, size, large, color, green
apple, size, large, color, red
car, type, coupe, fuel, diesel
car, type, coupe, fuel, gas
car, type, sedan, fuel, diesel
car, type, sedan, fuel, gas
It's probably elegant enough. The consumer of the CVS just needs to know that the name-value pairs follow the pattern "name_x" and "value_x". The list of NVP ends when it reaches the first "name_x" and "value_x" that is empty.