Discussion about whether or not respect OOP/MVC Data Model Design when working with SQL-Based Database -

i'd peoples opinion concept on data modeling vs sql (oop vs select statement) on following matter: when studying mvc , oop concepts, it's easy see natural form of things in follow:

<php? | <% | <# class country{ _name:string; _language:string; } class estate{ _name:string; _country:country; } ?> | % | #> 

the point i'm trying simple parent-child relation. now, i've seen working on extjs + php , jsf 2.0 (java using converter), it's quite common see dao (data access object) being defined sort of this:

abstract class countrydao{ public function getcountry();//this function returns arraylist. public function getcountrybyid(countryid:integer);//this function returns country object; ... } class estatedao{ public function getestate(){ $sql = "select * tbl_estate"; //[your favorite way comunicate database here] //declare arraylist or store result: $arraylist; while($result = $resultset.next()){ $obj = new estate(); $obj->name = $result['name']; $obj->country = new countrydao().getcountrybyid($result['country_id']); $arraylist->additem($obj); } } } 

sorry mixing language syntaxes, i'm trying not attached language, concept

now finalize thoughts: right concept, according theory. personally, don't think it's right disagree since it's logic of thing. in practice, real world don't ever shine want, see that:

if have 10 estates, i'll have 11 sql statements being executed on hard disk level (which according operational system's theory, it's slowest of sorts of stored memory).

if have 100 estates, i'll have 101 sql statements being executed.

if have n estates, i'll have n+1 sql statements being executed.

my final point: think can real pain if you're modeling data entity might have thousands of records. because of line of thought, refused use kind of design logic while working flex + php. i'm trying build application have both flex , extjs viewers , php server-side, wanna sure i'm not choosing hardest path insignificant reason. safer not respect mvc/oop logic? exaggerating? think sql vs data model on oop?

thanks.

i suggest stay away dao implementations directly map onto db table. instead should data mapper pattern.

basically idea is, that, within model layer, separate business logic domain objects , db interaction data mappers. way , when need fetch collection of items (like details of 100 estates ), mapper performs 1 or 2 queries, , dumps data in domain object, deals collections.

and each mapper can deal more 1 db table. goal mapper take domain object , interface storage. there not law says domain object can use 1 table.

in case, mvc design pattern has nothing store information. deals separation of user interface , domain business logic. sql interaction ( if use sql database , not other data source ) buried deep inside model layer.

i recommend read article: gui architectures martin fowler.

also, if dealing php, might find this comment useful.