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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.