MVC data design problem with Zend framework.

Question

If I have a class representing access to one table in my database in a class:table relationship, so I can hide table details in one class. But as any useful application I have to query several tables. How can I accomodate this using the class:table design?

Answer 1

What do you mean by

But as any useful application I have to query several tables.

Answer 2

Hi,

There's a couple of different ways you can achieve this, however, which one you choose really depends on your circumstances.

1) Break the connection between your objects and your database

Write your objects to have no connection with your database tables. First normalise your database tables, then, look at how the user of your application will interact with your data. Model the data to objects, but don't tie each object to the table (ie with a Zend_DB_Table_Abstract class)

Once you have established your objects, then write mapper classes which map your objects back to the relevant tables in your database. These are the classes which extend Zend_DB_Table (if appropriate).

You can handle joins in two ways, either map the joins through the Zend_DB_Table relationship functionallity, or, (IMHO a better choice) just use Zend_DB_Select to make the relevant methods within your your mapper class.

So you've then got two classes (probably per table, but not always)

Person PersonMapper

In your code, when you want to work with some objects, either create a new object

$person = new Person();
$person->setName('andrew taylor');

Then write pass it to the mapper to save it:

$personMapper = new PersonMapper();
$pesonnMapper->save($person);

Or, do it the other way:

$personMapper = new PersonMapper();
$person = personMapper->load(29);

$person->setName('joe bloggs');
$personMapper->save($person);

The next step on from here would be a collection class based on the SPL:

$personList = $personMapper->loadAllMen();

foreach($personList AS $person) {

 echo $person->getName();
}

Where $personMapper->loadAllMen() is a method like:

$select = $this->select();
$select=>where('gender = "Male"');
$zendDbRows = this->fetchAll($select);

return new PersonList($zendDbRows);

2) MySQL Views

If you have a lot of joined tables where there is one row per join, so, you're joining customer information based on an id in your orders table, and you're doing it read-only (so you don't want to update any information through the Zend_DB_Table adaptor) you create your normalised tables, then, a single view across the top. The view handles the joins behind the scenes so through Zend it feels like you're connecting to a single table.

There are some caveats with this, MySQL views do have some performance problems (which is why it's best on single row FK joins), and, they're strictly read only.