Separating business logic from data store logic

The recent thread on DB_Simple has prompted me to submit some of my thoughts on object oriented database programming. Consider the following project which involves tracking games for youth soccer leagues. Like many projects, it's much more complicated then it seems. There are a number of different game schedule formats each with assorted sort and filtering options. Most require coach information. Some require referee information. A given team may have multiple games. A given referee may be assigned to multiple games. Games are played on different fields, teams can be from different leagues, there can be different rules and all sorts of other good stuff. Given these rather sketchy requirements we can start to design some business objects such as game,team,coach,referee,field etc. We need to be able to do things like: $field =& $game->getField(); $fieldName = $field->getName(); $homeTeam =& $game->getHomeTeam(); $homeTeamCoach =& $homeTeam->getCoach(); $centerRef =& $game->getCenterRef(); $centerRefName = $centerRef->getFullName(); For the sake of discussion lets assume that these business objects are derived from a common class called BO_Item. BO_Item objects are persist able but they themselves only support a simple persistent interface (methods like dnInsert,dbUpdate) which in turn relies on a separate object called BO_ItemPersist to actually persist. Moving all the nitty gritty database details to another class allows the developer to focus on the business object. We can also create different BO_ItemPersist objects for a given BO_Item class thus enabling the use of different object stores. This is similar (in concept) to what the LiveUser package does with containers. So the object could be persisted in a sql database, an xml file, an LDAP or whatever with little or no impact on the business logic layer. IMHO, the lack of a standard persist able business object which in turn supports a standard persistent interface has led to the creation of multiple conflicting PEAR databases interfaces as well as to low level data store classes which try to do too many things. ===================================================== The second missing PEAR piece is a standard container for multiple BO_Item's. Let's assume that we are going to use a sql database as a persistent store. A simplified view of the schema might look like this: games (Entry for each game) game_team (home team, away team etc) teams (Entry for each team) team_person (head coach, asst coach, player etc) persons game_person(center referee, assistant referees etc) persons fields (Where games are played) How to load all this data in from the database in a reasonably efficient fashion? One approach is use a minimal number of complex queries. These queries get to be very long indeed (remember the schema presented is greatly simplified) but I suppose some of the PEAR classes used to build queries might help. Each row returned from the query will end up with many different columns. It quickly becomes a pain to then extract the relevant information and store it in the BO_Item objects. Furthermore, there will be a great deal of redundant information. For example, let's say that a given team plays 5 games. You end up getting 5 copies of the same team information. Even with database caching it's an ugly solution. A second approach would be to "query on demand". The line $homeTeam =& $game->getHomeTeam(); would go out and query the database for the desired team for a particular game. This avoids the long complex queries and makes it easier to map sql data to the business objects but results in many small queries and you will still end up with duplicate data for each team. Furthermore, it becomes very difficult to do sorting and additional filtering. My solution was to create a collection class (BO_Items) capable of holding a set of individual business objects. $gameIDs = "23,45,67"; /* Assume that we somehow know what games to present */ $games->loadForIDs($gameIDs); /* Load in the collection of games */ $teamIDs = $games->getTeamIDs(); /* Get a distinct list of all teams */ $teams->loadForIDs($teamIDs); /* Query team information */ $coachIDs = $teams->getCoachIDs(); /* In this case, we only want the coaches */ $persons->loadForIDs($coachIDs); /* Bring the coaches in */ $refereeIDs = $games->getRefereeIDs(); /* Need referees */ $persons->loadForIDs($refereeIDs); /* Can use the same people collection */ With this approach, the queries stay simple as does the object mapping. We minimize duplicate data. We compromise on the total number of queries. Basically, we do one query for each chunk of data that needs to be loaded. And of course, it's fairly easy to understand the code that's actually loading the data. Like DB_Item, the DB_Items class does not know any details on how to access a given object store. Instead, DB_Items also relies on DB_ItemPersist to query the data store for one or more items. The collection class provides random access to individual objects. For example: class Team extends DB_Item { function &getCoach() { global $persons(); return $persons->getForID($this->coachID); /* Get the desired person from the persons collection */ } } We also have have a BO_ItemIter class to provide simple sequential access to the collection set. $iter =& $games->getItemIter(); $iter->sort("someSuperComplicatedSortingRoutine"); while($game =& $iter->next()) { /* Process a game */ } ===================================================== To Summarize, here are the classes I think we need to make an application less dependent on data store details: BO_Item - Represent one business object such as a game or team. BO_Items - Represents a collection of business objects BO_ItemIter - Helper class used to cycle through items in a collection. Includes custom sorting and filtering capability. BO_ItemPersist - Data store specific information about a given BO_Item object. It's basically an interface which allows persisting and retrieving BO_Item objects from/to different data stores. It can also be used by admin classes to do things like table creation. And just to clarify, I am not trying to reinvent the entire database abstraction layer here. BO_ItemPersist could actually be a very simple wrapper to DB_DataObject or DB_Simple or to any of the ldap/xml/file classes. I am just trying to isolate the business logic layer from the data store area. And The Point Is? First of all, let me thank those of you that have been patient enough to read this far. Now that soccer season is over I am contemplating rewriting portions of my open source game scheduler to use more PEAR modules, especially the form stuff. If I do so then I intend to keep a fairly detailed log of my efforts and submit it as an example of how use PEAR. If anyone thinks that the notion of separating business logic from data store logic make any kind of sense at all then I'll tweak my code to match PEAR standards, post some examples and make a more formal PEAR package proposal. I am also curious to see if I have ended up reinventing someone else's wheel. I have looked through many of the PEAR classes without finding similiar functionality. I do know that there are plenty of similiar non-PEAR classes out there. Thanks again, Art