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

Question

I'd like peoples opinion about the concept on Data Modeling Vs SQL (OOP Vs Select Statement) on the following matter: When studying MVC and OOP concepts, it's easy to see a natural form of things like in the follow:

<php? | <% | <#

Class Country{
     _name:String;
     _language:String;      
}

Class Estate{
     _name:String;
     _country:Country;
}

?> | % | #>

The point that I'm trying to get is a simple parent-child relation. Now, as I've seen working on ExtJS + PHP and JSF 2.0 (Java using Converter), it's quite common to see a DAO (Data Access Object) being defined sort of like this:

Abstract class CountryDAO{
     public function getCountry();//This function returns an ArrayList.
     public function getCountryById(countryId:Integer);//This function returns a Country Object;
     ...
}

Class EstateDAO{
     public function getEstate(){
         $sql = "SELECT * FROM tbl_estate";
         //[your favorite way to comunicate with the database here]
         //Declare an ArrayList or something to store the 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 for mixing language syntaxes, I'm trying not to get too attached to language, just the concept

Now to finalize my thoughts: That is the right concept, according to theory. Personally, I don't think it's right to disagree with it since it's the logic of the thing. But as in practice, the real world don't ever shine as you want, what I see from that:

If I have 10 Estates, I'll have 11 SQL Statements being executed on a Hard Disk level (which according to Operational System's theory, it's the slowest of all sorts of stored memory).

If I have 100 Estates, I'll have 101 SQL Statements being executed.

If I have n Estates, I'll have n+1 SQL Statements being executed.

My final point: I think that can be a real pain if you're modeling a data entity that might have thousands of records. Because of that line of thought, I refused to use this kind of design logic while working with Flex + PHP. But now that I'm trying to build an application that will have both Flex and ExtJS as viewers and PHP as Server-Side, I just wanna be sure that I'm not choosing the hardest path for insignificant reason. Is it safer not to respect this MVC/OOP logic? Am I exaggerating? What do you think about SQL Vs Data Model on OOP?

Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

I would suggest to stay away from DAO implementations which directly map onto a DB table. You instead should look into data mapper pattern.

Basically the idea is, that, within the model layer, you separate business logic into domain objects and DB interaction into data mappers. This way , when you need to fetch a collection of items (like details of 100 estates ), the mapper performs only 1 or 2 queries, and dumps the data in your domain object, which deals with collections.

And each mapper can easily deal with more the one DB table. The goal for mapper is to take domain object and interface it with storage. There is not law which says that your domain object can use only one table.

In any case, the MVC design pattern has nothing to do with where you store the information. It only deals with separation of user interface and domain business logic. The SQL interaction ( if you even use SQL database and not some other data source ) is buried deep inside the model layer.

I would strongly recommend for you to read this article: GUI Architectures by Martin Fowler.

Also, if you are mostly dealing with PHP, you might find this comment useful.

Thanks, that was helpful. Now I think I know why I should consider learning how to use Doctrine ORM. — Marco Aurélio Deleu, Jul 09 '12 at 13:23

score 0 · Answer 2 · answered Jul 07 '12 at 04:15

You can have the best of both worlds. You don't need to store your objects in the way they are stored in memory, and even if you do, you can model the additional objects to be able to save them in an efficient way: Given:

Class Country{
     _name:String;
     _language:String;      
}
Class Estate{
     _name:String;
     _country:Country;
}

You could create:

Class EstateWithCountryInformation{
     _estateName:String;
     _countryName:String;
     _language:String;      
     getEstate();
     getCountry();
}

And only have the tables Country and EstateWithCountryInformation. That way you can get what you need in a single query. If you ever need to query just the Estate information you can also get it from the EstateWithCountryInformation table, or you could even create a DataBase view to simulate that table.

I don't know what you use to access the DB, but I've known some ORM with advanced mapping options, that allows you to take the classes you originally defined, and automatically mapped to the EstateWithCountryInformation like table, and when you query an Estate, and afterwards call new CountryDAO().getCountryById() they have the information cached on memory, so it doesn't do any additional DB access.

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

2 Answers2