Modeling objects with multiple table relationships

Question

I'm toying with Zend Framework and trying to use the "QuickStart" guide against a website I'm making just to see how the process would work. Forgive me if this answer is obvious, hopefully someone experienced can shed some light on this.

I have three database tables:

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL default '',
  `first` varchar(128) NOT NULL default '',
  `last` varchar(128) NOT NULL default '',
  `gender` enum('M','F') default NULL,
  `birthyear` year(4) default NULL,
  `postal` varchar(16) default NULL,
  `auth_method` enum('Default','OpenID','Facebook','Disabled') NOT NULL default 'Default',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_password` (
  `user_id` int(11) NOT NULL,
  `password` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_metadata` (
  `user_id` int(11) NOT NULL default '0',
  `signup_date` datetime default NULL,
  `signup_ip` varchar(15) default NULL,
  `last_login_date` datetime default NULL,
  `last_login_ip` varchar(15) default NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I want to create a User model that uses all three tables in certain situations. E.g., the metadata table is accessed if/when the meta data is needed. The user_password table is accessed only if the 'Default' auth_method is set. I'll likely be adding a profile table later on that I would like to be able to access from the user model.

What is the best way to do this with ZF and why?

Answer 1

I would say that the best way to do this with ZF is by using Doctrine.

Answer 2

Basically instead of using Zend_Db_Table use more general Zend_Db_Select or Zend_Db_Statement to retrieve data.

BTW. You might want to access password data not directly in User model, but rather in your User auth class derived from Zend_Auth_Adapter.

Answer 3

In a nutshell I would create a model for each table, not one model that accesses all three. I would then define relationships between the tables.

To be honest it seems not very "DRY" to have to create a model for each table but that is what I see done repeatedly in the various examples online and it is what I have done in the handful of projects that I have created with the Zend Framework. If anyone has a better way of handling this I hope the will post it here.

Answer 4

class Users extends Zend_Db_Table_Abstract
{
    protected $_name = 'users';
    protected $_rowClass = 'User';
    protected $_dependentTables = array ('UserMetadata', 'UserPassword');

...

class UserMetadata extends Zend_Db_Table_Abstract
{
    protected $_name = 'user_metadata';
    protected $_referenceMap = array (
    'Users'=> array (
    'columns'=>'user_id',
    'refTableClass'=>'Users',
    'refColumns'=>'id'
    )
    );

...

class UserPassword extends Zend_Db_Table_Abstract
{
    protected $_name = 'user_password';
    protected $_referenceMap = array (
    'Users'=> array (
    'columns'=>'user_id',
    'refTableClass'=>'Users',
    'refColumns'=>'id'
    )
    );

Fetching data:

$id = //get your user id from somewhere

$users = new Users();
$user = $users->fetchRow('id=?', $id);
if ($user->authMethod == 0)
{
    $metadata = $user->findDependentRowset('UserMetadata')->current();
}

or

$user = $users->fetchRow($users->select()
              ->where('gender=?, 'M')
              ->order('email ASC');

... etc.

Inserting data:

$newRow = $users->fetchNew();
$newRow->email = [e-mail];
$newRow->save();

or

$users = new Users();
$data = array('email'     => '[e-mail]',
              'firstname' => 'Me');
$users->insert($data);

Updating:

$user->email = '[e-mail]';
$user->save();

Deleting a row:

$user->delete();

Using transaction:

$db->beginTransaction();
$db->commit();
$db->rollback();

etc... it's all in the ZF Manual!

Answer 5

Just a few notes straight off the bat:

The first User table, seems more like a Person table, except for the auth method, but you could put that in the User_Password table, which you could probably rename User. The User_Metadata table seems like it could just be amalgamated with the User_Password/User table.

Now, even without those changes, you have three distinct tables, with three distinct concepts, if you model each of those separate as different classes, and then had a UserModel class as a facade of sorts to access each, it would likely make it easier.