MVC::Normalized_Database(?);

[texdc]

First of all, I've searched through the existing posts looking for this a number of different ways and can't seem to find it. If you know where the answer already exists, please redirect! Thanks!

Note: Much of this is directly related to the DAO

I'm attempting to write my first app using the MVC design pattern, and am having difficulty modeling my data. In order to normalize my database, I've spread user data across the following tables:

mysql> TABLE users;
+-------+-------+--------+--------+-----+----+----------+-------------+
|user_id|auth_id|username|password|first|last|date_added|date_modified|
+-------+-------+--------+--------+-----+----+----------+-------------+

mysql> TABLE authorizations
+-------+---------+
|auth_id|auth_name|
+-------+---------+

mysql> TABLE ip_addresses
+-----+----------+
|ip_id|ip_address|
+-----+----------+

mysql> TABLE user_ips
+-----+-------+-----+
|ui_id|user_id|ip_id|
+-----+-------+-----+

mysql> TABLE groups
+--------+----------+---------+
|group_id|group_name|directory|
+--------+----------+---------+

mysql> TABLE user_groups
+-----+-------+--------+
|ug_id|user_id|group_id|
+-----+-------+--------+

Now, as I understand things, there should be a DAO for each table in the database, right? Wouldn't that be overkill? If so, and I use only a single "user_DAO", almost all of my stored queries are going to use long/complicated joins. However, if I do use a seperate DAO/model for every table, how would I set things up?

/** Note:
 * The models are passed the database connection and instanciate their own appropriate DAO
 */
class UserController extends Controller {
    // ??????????
    var $ip;
    var $group;

    function UserController(&$dbc) {
        $this->model =& new UserModel($dbc);
        $this->view =& new UserView();
        // ??????????
        $this->ip =& new ipController($dbc);
        $this->group =& new GroupController($dbc);
    }

    function getUser($id, $view='list');
        $user = $this->model->getUser($id);
        // then what about ip and groups info?
        $ip = $this->ip->getIPbyUser($user['id']);
        $group = $this->group->getGroupByUser($user['id']);
        return $this->view->getView($user, $ip, $group, $view);
    }

    // etc...

} 

Also, how do I avoid this redundancy:

// Controller
function getUser($id) {
    $this->model->getUser($id);
    // ...

// Model
function getUser($id) {
    $this->dao->getUser($id);
    // ...

// DAO
function getUser($id);
    // ... 

Thanks in advance for your help!

Peace,
texdc

[Captain Proton]

First of all, I'd like to say that DAO's are not necessarily a part of MVC. My guess is that you picked up that idea because so many threads here about MVC also presented code with a DAO in it.

You are right though, having a DAO for each table in your database is not necessary. Some tables are related to other tables, such as a user_ips table which is related to the users table. I am not exactly sure what the relation between those tables is, but I think that the UserDAO can be responsible for both of them. When there's a one-to-many relation between tables, the 'many' table can be part of the 'one' table DAO's responsibility.

Martin Fowler's Patterns of Enterprise Application Architecture book explains it better. Take a look at the Object-Relational Structural Patterns here, especially the Dependent Mapping, which I think applies to your situation.

From looking at your code, I think you understand the MVC pattern (if there is such a thing as 'the' MVC pattern, but that's another story..) a bit wrong. The fact that you're initiating a ipController and a groupController inside the UserController does not make any sense to me. A Controller's job should be to access and manipulate model data and pass it on to View objects for one specific action / task of an application. In your code this 'task' would be to view information about a User.

Here's some example code how I would structure the classes:

class UserController
{
    var $user;
    
    function UserController(&$dbc)
    {
        // Initiate the DAO
        $userDAO = new UserDAO($dbc);
        
        // Get an array of User objects
        $users = $userDAO->getUsers();
        
        // Pass them on to the View
        $this->view = new UserView($users);
    }
}

class UserDAO
{
    ...
    
    function getUsers()
    {
        // This is pseudo-code of course, but I'm sure you understand the meaning
        $userRows = 'SELECT * FROM users';
        foreach ($userRows as $userRow)
        {
            $userObject = new User($userRow);
            
            // Foreach User object execute a query to get the IP addresses
            $userIPs = 'SELECT ip_id FROM user_ips WHERE user_id = ' . $userObject->getID();
            $userObject->setIPs($userIPs);
            
            // Do the same for the groups and authorizations
            ...
        }
        
        return $userObjects;
    }
}

class User
{
    var $ips;
    
    ...
    
    function setIPs($ipArray)
    {
        $this->ips = $ipArray;
    }
    
    function getIPs()
    {
        return $this->ips;
    }
}

class UserView
{
    function UserView(&$userObjects)
    {
        foreach ($userObjects as $user)
        {
            echo $user->getName() . '<br>';
            
            foreach ($user->getIPS() as $ip)
            {
                echo "IP: $ip<br>";
            }
        }
    }
} 

You can avoid the redundancy you mention by following this pattern:

- Use a DAO to load the Model objects
- Pass the Model objects directly to a View object

i.e.:

$dao = new UserDAO($databaseConnection);
$userObject = $dao->findByID($modelObjectID);
$view = new UserView($userObject); 

[texdc]

Captain Proton,

Thanks very much for your response. I believe you have pointed me in the right direction to get me past this "road block" I've been fighting. I'll look your code over in more depth later, and perhaps rework it to make sure I understand, especially that last little bit about redundancy.

I'll check out the Fowler book as well, asap.

Thanks again!
-texdc

[Dr Livingston]

For the roles (grouped) based authentications I have a Dao for Users, Roles And Privleleges; I have another one for verifying a Log In etc etc...

In most of the Daos there is about 8 queries each so I wouldn't worry too much about the actual Dao it's self, but more in the way you use them.



--EDIT--

Follow this link and you'll see how I work things out...

http://www.sitepointforums.com/showt...hreadid=116318

Thanks due to MKRZ for helping out with some scripting

[MattR]

EWW! Are you really advocating using PHP to perform the JOIN instead of the DBMS?

This is why I hate MVCs in general, and especially the pervasive 'OO is the OneTruePath' ideology amongst practitioners.

[Captain Proton]

I'll say it again, DAO (which is a form of object-relational mapping) has *nothing* to do with MVC, which is about separating the concerns of application data, presentation and interface control.

You're right though, you should leave the JOINing of tables to the database system, don't do it in PHP. That may be the easiest way to implement but it is certainly not the most efficient way. There are better ways to map objects to database tables, like the ones described in Martin Fowler's PoEAA.

[sweatje]

Joins definitly belong on the DB side of the fence. This may be a problem with the "scope" of your model class. In general, you should be modeling somthing from the real world, like a customer or an order, not a database table. The model should be smart enough to run efficient queries within the domain of that model.

I use Oracle as a database at work, and many of my models simply end up as a mapping of database package functions to class methods, with some possible parameter binding. This keeps the bulk of the "business logic" in the database where it belongs. (I guess you could think of all of my PHP code as a giant "View" with the "Model" being the database )

[texdc]

Interesting posts.

My host only offers MySQL, and that without BDB or InnoDB type tables. So, I'm stuck doing my joins in PHP. It may not be the most efficient way, but that's the extent of my capabilities and knowlege. Thanks, though.

I understand that a DAO is not part of the MVC. I'm simply building off Harry's tutorials at phppatterns.com. Please keep in mind that I'm still trying to get my head around the MVC concept, and the part I'm having the most difficulty with is the model. After that, where to put specific methods, the controller, or the model. So, please bear with me.

Thanks again!
-texdc

[Dr Livingston]

Umm... Using PHP for JOINs ? Never used them myself personally, though for the general interest since the matter has now been brought up, here is the database SQL I use... You should get the general idea of the fields so I won't post the DB schema

function searchAllPriveleges__ByRoleId($id = 0) { # privelege id 'null' by default
            $sql = "SELECT 
                rolepriveleges.roleid, 
                rolepriveleges.privid, 
                priveleges.privid             AS ID, 
                priveleges.privdescrip        AS DESCRIP 
                FROM 
                rolepriveleges, priveleges 
                WHERE 
                rolepriveleges.roleid = '". $id ."' 
                AND 
                rolepriveleges.privid = priveleges.privid ORDER BY priveleges.privid";
                
            return $this -> retrieve($sql);
        }

function & totalRows() {
            $sql = "SELECT COUNT(*) FROM roles";
            return $this -> retrieve($sql);
        }
        
        function searchAll() {
            $sql = "SELECT * FROM roles ORDER BY roleid";
            return $this -> retrieve($sql);
        }
        
        function searchAllRoles__ByAuthorId($id = 0) { # role id 'null' by default
            $sql = "SELECT 
                authorroles.authid, 
                authorroles.roleid, 
                roles.roleid             AS ID, 
                roles.rolelimit         AS CAP, 
                roles.roledescrip         AS DESCRIP 
                FROM 
                authorroles, roles 
                WHERE 
                authorroles.authid = '". $id ."' 
                AND 
                authorroles.roleid = roles.roleid ORDER BY roles.roleid";
                
            return $this -> retrieve($sql);
        }


function searchAll__LogsByCommentId($id) { #
            $sql = "SELECT 
                logs.logid            AS LOGID, 
                logs.logdate        AS LOGDATE, 
                logs.logtitle        AS LOGTITLE, 
                
                authors.authid        AS AUTHID, 
                authors.authfname    AS AUTHFNAME, 
                authors.authlname    AS AUTHLNAME, 
                
                authorlogs.logid, authorlogs.authid, 
                usercomments.commentid, usercomments.logid  
                FROM 
                logs, authors, authorlogs, usercomments  
                WHERE 
                usercomments.commentid = '". $id ."' 
                AND 
                usercomments.logid = logs.logid 
                AND 
                authorlogs.logid = logs.logid 
                AND 
                authorlogs.authid = authors.authid";
            
            return $this -> retrieve($sql);
        }
        
        function searchAll__ByLogDate($param = '') { # 
            $sql = "SELECT 
                logs.logid                     AS LOGID,
                logs.logdate                AS LOGDATE,
                logs.logtitle                AS LOGTITLE,
                logs.logmessage                AS LOGMESSAGE,
                logs.logstatus                AS LOGSTATUS,
                logs.logacceptance            AS LOGACCEPT,
                
                authors.authid                AS AUTHID,
                authors.authfname            AS AUTHFNAME,
                authors.authlname            AS AUTHLNAME,
                
                authorlogs.logid, authorlogs.authid 
                FROM
                logs, authors, authorlogs 
                WHERE 
                authorlogs.authid = authors.authid 
                AND 
                authorlogs.logid = logs.logid 
                ORDER BY logs.logdate DESC" . $param;
                
            return $this -> retrieve($sql);
        }
        
        function searchAll__ByLogId($id = 0) { # get all log and author by logid
            $sql = "SELECT 
                logs.logid                     AS LOGID,
                logs.logdate                AS LOGDATE,
                logs.logtitle                AS LOGTITLE,
                logs.logmessage                AS LOGMESSAGE,
                logs.logstatus                AS LOGSTATUS,
                logs.logacceptance            AS LOGACCEPT,
                
                authors.authid                AS AUTHID,
                authors.authfname            AS AUTHFNAME,
                authors.authlname            AS AUTHLNAME,
                
                authorlogs.logid, authorlogs.authid 
                FROM
                logs, authors, authorlogs 
                WHERE 
                authorlogs.logid = '". $id ."' 
                AND 
                authorlogs.authid = authors.authid  
                AND 
                authorlogs.logid = logs.logid 
                ORDER BY logs.logdate DESC";
                
            return $this -> retrieve($sql);
        }
        
        function searchAll__ByAllResults($id = 0, $param = '') { # $id (ie all comments based on one logid - not required here)
            $sql = "SELECT 
                logs.logid                     AS LOGID,
                logs.logdate                AS LOGDATE,
                logs.logtitle                AS LOGTITLE,
                logs.logmessage                AS LOGMESSAGE,
                logs.logstatus                AS LOGSTATUS,
                logs.logacceptance            AS LOGACCEPT,
                
                authors.authid                AS AUTHID,
                authors.authfname            AS AUTHFNAME,
                authors.authlname            AS AUTHLNAME,
                
                authorlogs.logid, authorlogs.authid 
                FROM
                logs, authors, authorlogs 
                WHERE 
                authorlogs.authid = authors.authid 
                AND 
                authorlogs.logid = logs.logid 
                ORDER BY logs.logdate DESC". $param;
                
            return $this -> retrieve($sql);
        }
        
        function insert__AddNewLog($title, $message, $notes, $status, $comments, $authid) { #
            $sql = "INSERT INTO logs SET 
                logdate = now(), 
                logtitle = '". $title ."', 
                logmessage = '". $message ."', 
                logstatus = '". $status ."', 
                logacceptance = '". $comments ."'";
            $this -> update($sql);
            $insert = (int) mysql_insert_id(); // logid
            
            $sql = "INSERT INTO authorlogs SET 
                logid = '". $insert ."',
                authid = '". $authid ."'";
            $this -> update($sql);
            
            $sql = "INSERT INTO authornotes SET 
                logid = '". $insert ."',
                authid = '". $authid ."',
                authnotes = '". $notes ."'";
            $this -> update($sql);
        }
    } 

...

Anyway, does anyone know of a decent article on JOINs if the above isn't the best path to walk down... ?

Thanks

[HarryF]

At some point think I need to write version 2 of the articles on phppatterns (perhaps when PHP 5 hits the streets).

Where DAOs are concerned, think I need to emphasise the point that they're prime purpose is to limit the number of times you need to repeat the same SQL statement to as few as possible as well as "centralizing" queries in an easy to find / maintain layer.

Think it's not worth trying to use them too hard as a mechanism to be database independent (although it helps not to have queries repeated). Sun promote this as a reason for DAO's here but in practice, most databases have very varying SQL support and often the "temptation" to use the non standard stuff is overwhelming. Think that's really a Sun vs. Oracle issue.

The other thing about DAOs is, implemented as I suggested, they won't entirely limit the impact of, say, a change in a column name to the DAO classes. In my example I had column names turning up in the "view". Another layer is needed to represent a single row from a query which provides accessor methods which remain the same while the underlying fields they fetch may vary. The example of Articles -> Article in the Factory method might help you there. Articles could be a DAO itself or perhaps a class which uses a DAO.

Also it's worth looking at what "persistence layers" like DB_DataObject are doing.

[Dr Livingston]

Harry - Looking at...

http://www.phppatterns.com/index.php...leview/49/1/1/

I seem to understand this fully although a question on UML and the IMG you have on the page;

Does the - mean a property going to the class and + mean a property going from the class so to speak ?

Not to sure on UML you understand... Or maybe you can better explain this... Proberly

Thanks.

[Captain Proton]

I'm not Harry, but..
- = private, + = public

[sweatje]

Captain Proton is probably correct, my inital impression was -=attribute +=method, but those are already indicated by the line and () for methods

[Dr Livingston]

Thanks folks; Always wondered that is all... Why a box was split into 2 parts... Now I know and it helps...

[shoebox]

My host only offers MySQL, and that without BDB or InnoDB type tables. So, I'm stuck doing my joins in PHP. It may not be the most efficient way, but that's the extent of my capabilities and knowlege. Thanks, though.
-texdc

This is a problem that many people have, so it frustrates me when I see some the devs say use Postgres or some other dbtype that natively handles foreign key mapping within the db schema. Lots of us dont have this option

But, although you cannot explicitly define the table relationships on MYISAM tables, You CAN still do JOINS on the using a join clauses in your select queries. But I guess you know this, since this is exactly the "problem" you are trying to solve. I fear that using PHP to do your "joins" is going to create a large performance hit on your application because you will be running multiple queries to get the related data from different tables, and even if you don't see a huge performance hit now, you will see later on down the road, after your application has grown, and you are still doing things this way.

I would try and avoid this if I were you. Take a look at the Class Table Inheritance Pattern. This looks like the approach you are taking now.

On that note, you may see that a class for each table in your database is not really necessary.

In your situation you would simply use a SQL statement with a join clause in your User table and be done.

class User
{
 var $database;
 function &User($database)
 {
  $this->database = $database;
 }
 function &getUsers()
 {
  $sql = "insert massiv join string here";
  return $this->database->query($sql);
 }
} 

If you plan on the other tables having a relationship with something besides users you can simply incorporate another join there as well.

And if you don't like writing SQL for each class I would look into creating a class that generates the SQL for you (Query Object Pattern on MF's site).

HTH

[texdc]

shoebox: you're right on target on where I'm at, and where I'm going with this.

Here is what I've cooked up so far. I want to avoid passing the model itself to the view completely to keep them ignorant and separate. Currently, this solution does not allow that approach, but it's helping me understand the whole pattern better.

Note: The model will always instanciate the DAO and the DAO queries will always return a 'result' object.

class UserModel extends Model {
    var    $ID;
    var    $images;
    var    $authoriztion;
    var    $groups;
    var    $ips;
    
    function UserModel(&$dbc) {
        $this->dao =& new UserDao($dbc);
    }
    
    // there are a lot of queries here, rather than one huge JOIN
    // good or bad idea?
    // could also be split into specific functions
    function _setup($id) {
        $this->ID = $id;
        
        $athr =& $this->dao->getAuthorization($this->ID);
        $this->authorization = $athr->getRow();
        
        $grpr =& $this->dao->getGroups($this->ID);
        while ($group = $grpr->getRow()) {
            $this->groups[] = $group;
        }
        
        $ipsr =& $this->dao->getIPs($this->ID);
        while ($ip = $ipsr->getRow()) {
            $this->ips[] = $ip;
        }
        
        // alternate way of handling an array?
        $imgr =& $this->dao->getImages($this->ID);
        while ($image = $imgr->getRow()) {
            $i_id = array_shift($image);
            $this->images[$i_id] = $image;
        }
    }
    
    // not sure if this is the best way to handle this...
    function &get($ref) {
        switch (gettype($ref)) {
            case 'integer':
                $u =& $this->dao->getByID($ref);
                $this->_setup($ref);
                break;
            case 'string':
                $u =& $this->dao->getByName($ref);
                $this->_setup($u->getRow()['id']);
                break;
        }
        return $u;
    }
} 

I started down this path because I was looking at the Composite pattern in the GoF book. It sounded like it would work, but in putting it together, it may not be the best choice for a Model pattern, but I may be wrong. (I know I'm not implementing it in the code above; it was simply inspiration.) What about the Decorator pattern? Any comments or ideas?

Thanks again!
-texdc

[shoebox]

I want to avoid passing the model itself to the view completely to keep them ignorant and separate.

Why do you want to do this? Is it a design requirement? The reason I ask is because it seems to make things much easier when you pass a model to view, or view to model.

// there are a lot of queries here, rather than one huge JOIN
// good or bad idea?

Yes I think one large join would be better. Can someone back me up on this?

If you keep doing things the way you mentioned you it seems you will end up writing a custom method for each "getBy()" you want to do.
Perhaps this could be a generic "getById()" method in your parent Model class.

I'm not familiar with Composite.

what about something like this instead??
I'm not familiar with Composite.

<?
 class UserModel extends Model
 {
  function UserModel(&$dbc)
  {
   parent::model($dbc, 'users', 'id');
  }
 }
 class Model 
 {
  var $dbc;
  var $table;
  var $idField;
  
  function Model(&$dbc, $table, $idField)
  {
   $this->dbc = $dbc;
   $this->table = $table;
   $this->idField = $idField;
  }
  function getById($id)
  {
   $sql = "SELECT * FROM $this->table WHERE $this->primaryKey = $id";
   return $this->dbc->query($sql);
  }
  function getList()
  {
   $sql = "SELECT * FROM $this->table";
   return $this->dbc->query($sql);
  }
  function doInsertById($SqlFields, $SqlValues)
  {
   $sql = "INSERT INTO  $this->table FIELDS($SqlFields->getCommaSeparated()) VALUES($SqlValues->getCommaSeparated())";
   $this->dbc->query($sql);
   return $this->dbc->isSuccess();
  }
  function doUpdateById($id, $SqlFields, $SqlValues)
  {
   $sql = "UPDATE $this->table SET($SqlFields->getSetClause($SqlValues) WHERE $this->idField = $id";
   $this->dbc->query($sql);
   return $this->dbc->isSuccess();
  }
  function doDeleteById($ids)
  {
   if (is_array($ids)) {
    // handle mutliple deletes
   } else {
    $sql = "DELETE FROM $this->table WHERE $this->idField = $ids";
    $this->dbc->query($sql);
    return $this->dbc->isSuccess();
   }
  }
 }
 

<?

[sweatje]

I'm not familiar with Composite.

But you just described it in action in your prior statement Composite is aggregating objects, so if you pass a model to a view and use them together, you are using a form of the Composite design pattern

[MattR]

I use Oracle as a database at work, and many of my models simply end up as a mapping of database package functions to class methods, with some possible parameter binding. This keeps the bulk of the "business logic" in the database where it belongs. (I guess you could think of all of my PHP code as a giant "View" with the "Model" being the database )

Yay! Someone knows what they are doing! That's exactly how it should work.

[Taoism]

Yay! Someone knows what they are doing! [img]images/smilies/smile.gif[/img] That's exactly how it should work.

I too use Oracle at work and also end up doing a lot of mapping like Jason indicates in his message further up this thread.

However, I would like to add that putting the business logic in the DB is not "exactly how it should work". It is simply one way of approaching a solution. And indeed, I think some people (many?) believe the business logic needs to be moved out of databases and into another tier - there are whole companies founded and doing strong business around the concept of "business rules" or "business intelligence" being its own component in an overall architecture....

Just my soap-boxy (mmmm...new word) $0.02 on the subject....

Cheers,
Keith.

[MattR]

However, I would like to add that putting the business logic in the DB is not "exactly how it should work". It is simply one way of approaching a solution.

But it's the right one.

And indeed, I think some people (many?) believe the business logic needs to be moved out of databases and into another tier - there are whole companies founded and doing strong business around the concept of "business rules" or "business intelligence" being its own component in an overall architecture....

It depends, really, on your definition of 'business rules', but these people are wrong.

As the old saying goes -- what once was old is now new again. Back-in-the-day people had to write their own data storage mechanisms. Lots of individual libraries which were buggy, hard to maintain, etc. Business rules were handled in the application (also buggy) and so data was, unfortunately, more often invalid than it should be.

Then, the world saw the light and Codd came up with the relational database management system. The whole (well, one of the main ones, I guess) concept of the RDBMS is, in laymans terms, that you encapsulate your business rules, in the form of constraints, into the management system so that you guarantee that the data in your database is logically sound.

In essence, the RDBMS is the intelligence behind business.

The best part is that you, typically, don’t have to code how to implement the business rules – you tell the RDBMS what to enforce and it does the rest. This is cross-platform, easy to read and understand, and virtually bug-free (provided you know what the business rules are ).

If you’re interested in more, check out “What Not How: The Business Rules Approach to Application Development”.

[sweatje]

This figure from my article Industrial Strength MVC shows one way to architect a MVC application, including some of the recent discussion on this thread regarding moving business logic down to the database layer. I think picturing this "application stack" can help formalize some of the ideas of how to implement "Model-View-Controller" style frameworks in PHP. The yellow bars show the particular libraries I used in the article, but these could easily be changed out for your favorite RDBMS (except for MySQL), db abstraction layer, MVC controller framework and templating engine.

HTH

[MattR]

Much of the problem comes from the industry viewing the (R)DBMS as nothing more than a 'persistence layer' or 'data store'.

The RDBMS is much more than simply data.

[sweatje]

Much of the problem comes from the industry viewing the (R)DBMS as nothing more than a 'persistence layer' or 'data store'.

The RDBMS is much more than simply data.

That was the idea I was trying to caputure in having the curly brace denoting the Model extend down into the database layer

[MattR]

It's important enough that it can't be re-stated too often.