title: Designing the data access layer for generic database-models
body: <p>I am an rather inexperienced junior-developer, working for a startup in germany. I am currently refactoring a highly scattered code base, trying to optimize the design a bit ...</p>
  
  <p><strong>Problem</strong></p>
  
  <p>When implementing the data access layer, we can choose to follow the principles of the DAO pattern. This works well as long as the entities we deal with are rather specific (eg. User, Invoice, Account, etc.). 
  But when we have a more generic database model we may frequently feel the need to query for specific result sets that are compositions of several tables. Often it is not obvious which data access object we should assign query methods to.</p>
  
  <p><strong>Example</strong> </p>
  
  <p>Take the OSM (openstreetmap.org) database: Effectively there are just Nodes, Ways and Relations that make up representations of many different objects (such as streets, buildings, traffic signs etc.). When querying for an amenity it may be represented by a single Node (a single point, e.g. a memorial), a Way (a path, lets say the outlines of museum) or collection of Ways (e.g. the buildings of a university campus). </p>
  
  <p>Besides that I may only be interested in a fraction of the information available, and it might be overhead to fetch full entities. Or I may use database specific functions in my queries, which lead to results that abstract even further from the underlying entity model.</p>
  
  <p>I give you an example in code (in java, using the spring-frameworks repository interface):</p>
  
  <pre><code>public class IWayRepository extends Repository&lt;Way, Long&gt; {
      ...
      @Query(value = "select distinct ways.tags-&gt;'addr:interpolation' as type,"
        + "trim(both '{}' from text(ways.nodes)) as nodes from nodes, way_nodes, ways "
        + "where nodes.tags-&gt;'addr:street'=? and way_nodes.node_id=nodes.id "
        + "and ways.id=way_nodes.way_id "
        + "and st_intersects(st_geometryfromtext(?,4326),nodes.geom)"
        + "and exist(ways.tags,'addr:interpolation')=true", nativeQuery = true)
      List&lt;Map&lt;String, Object&gt;&gt; findAdressInterpolationInfoForStreetInBbox(String street,
        String bbox);
      ...
  }
  </code></pre>
  
  <p>The code queries over several tables, alters data with a function on the database-layer and returns an extract of data that hardly fits into the domain of the given entity. </p>
  
  <p><strong>Idea</strong></p>
  
  <p>To improve the design there are coming two options coming to my mind for now. </p>
  
  <ul>
  <li>Make up a more specific data access class: The AddressRepository, where I access bare result sets. But I dont really like that idea. </li>
  <li>Make up a more generic Service class: A LocalizationService for example.</li>
  </ul>
  
  <p><strong>Question</strong></p>
  
  <p>Are there any established practises to deal with such situations? How do I design the data access to generic database-models. Are there patterns that one should follow?</p>
  
  <p>Thank you in advance!</p>