I am thinking of a very flexible application that will build a single entity object from a lot of tables joins. example for an object "Car"
with a code something like this
SELECT e.id, a1.attr_value as "cost", a2.attr_value as "color", a3.attr_value as "size", . . . FROM entity e LEFT OUTER JOIN attrib a1 ON (e.entity_id = a1.entity_id AND a1.attr_name = 'cost') LEFT OUTER JOIN attrib a2 ON (e.entity_id = a2.entity_id AND a2.attr_name = 'color') LEFT OUTER JOIN attrib a2 ON (e.entity_id = a3.entity_id AND a3.attr_name = 'size')
Now as said here by @Bill Karwin (credits to him also from the example code above)https://stackoverflow.com/questions/192892/performance-of-large-eav-open-schema-systems-on-sql-server
The solution is to fetch the attributes in rows instead of columns, and write a class in application code to loop over these rows, assigning the values into object properties one by one.
In this scenario both code and SQL can create "Car" object filled the data in each property but what is the general rule for this kind of scenario? Put all the burden of building the data in the Database side or in the Code side?
As I remember one of my senior colleague before said that
if the SQL side can take care of everything do it there so the code can just display the data.
is this right?