Design Pattern: Table Data Gateway

A table data gateway is a gateway?an interface to a separate component or subsystem?that provides access to its data by abstracting an entire database table (or entire file, or entire tree, or entire other data structure) behind a single interface object. All of the standard CRUD operations are performed by accessing methods of the table data gateway. For SQL data stores, table data gateway methods correspond directly to SQL statements: DELETE, INSERT, SELECT, UPDATE. The method arguments are mapped into SQL statements directly. In general, table data gateway will provide all methods for accessing the data store, including Finder Methods. If complex query statements are needed, table data gateway may accept attribute/value pairs and construct a ?generic query? internally.

Table Data Gateway class diagram

The key distinction between table data gateway and a Row Data Gateway is that a table gateway encapsulates the entire data structure behind a single object or class, while a row gateway encapsulates a single entry behind one instance of the gateway object. When the application is dealing with only one object at a time, the two are very similar. However where a row data gateway may use a separate Finder class or method, a table data gateway will provide the lookup method itself.

The difference between table data gateway and Table Module is that a table data gateway contains no business logic. Table modules are expected to contain business and/or application logic, as well as data access.

Applications commonly need access to only one object of a given class at a time, or to the results of aggregate functions (COUNT, SUM, etc.) provided by the database system. This is the interface provided by a table data gateway.

Table data gateway provides only a single point of access to the data store. Because of this, it is nearly impossible to implement this pattern in a stateful manner. Table data gateway should not try to track state, or to remember data between calls. Dealing with lists of values is challenging, especially if using a cursor or other database-specific, stateful entity. In these cases it is particularly important to remove the statefulness from the pattern.

Record Set, or some other pattern that can extract the entire list from the table in a single logical interaction, is an important partner. Even with record set to resolve the stateful nature of list traversal, table data gateway operations are performed one at a time.

Table data gateway is particularly usable when the access code is template-based or automatically generated. Implementing record sets then becomes a matter of storing a link to the appropriate gateway object with the record data. For many applications, a single table data gateway class can be used for all data access. Because PHP uses hashes as a core data type, an array of key/value pairs makes good sense.

Fowler does some hand-wringing in (PoEAA) bemoaning the various ways that table data gateway can lose compile-time type checking if they return generic objects like maps. This is more a problem with Java than with the table data gateway pattern, and obviously any pattern that needs to return aggregate data structures will have the same risk. (In C++, for example, you could create a template gateway class and instantiate a different class to get different return types. But that’s an awful lot of work for not much reward.)


In general, use table data gateway. This is the simplest of the interface patterns. It is more stable than row data gateway, since there is little or no tendency to ?drift.? It is amenable to machine-generated code, and fairly easy to implement by hand. For simple applications, a single class can serve as the gateway for all data tables.

When application data is not totally table-oriented (as in the example application), or when a single table may store values for multiple different purposes (see Class- and Single- Table Inheritance, below, for examples), table data gateway is challenged. Implementation is still possible, however, if some external mechanism is available for disambiguation. In the case of single table inheritance, for example, the gateway could return all field values (including some NULL values) to a factory (virtual constructor) that constructed and returned appropriate objects.

In general, use the ?iterator test? to determine the suitability of this pattern for your application: if all operations involving the data can be implemented using iterators (one-at-a-time access), then table data gateway is a viable pattern.


Avoid using table data gateway if the ?iterator test? fails. If the application requires access to multiple objects of the same class simultaneously, or if the application requires that objects be related to each other in a complex fashion (such as a tree or graph structure) then table data gateway may not be appropriate.

Of course, in-memory representation of the objects is possible, with table data gateway providing marshalling services and no more. But failure of the iterator test is a good indicator that some thought about the data layer is needed.

Beware of data models that store relationships between objects in separate tables. Having to use two or more different data gateways to get or set relationship data is likely to cause problems. In this scenario a more ?domain aware? pattern is probably indicated: consider active record or an object/relational mapper.

As with row data gateway, using a table data gateway can mean distributing the application’s data model through many separate classes. This will cause headaches when updating the code unless you are careful. For most data gateways it is possible to centralize the access code using composition (has-a) or inheritance (is-a) with a single core gateway class.


// IOU: A big mess o' PHP code


Both Data Mappers and table modules can be built on top of table data gateway classes, especially when the gateway classes are standardized. Using automatic code generation, or using a single core class with table-specific instances, or using database metadata to extract table details, can provide a simplified data layer interface so that your mapper or table module consists of nothing but object or application logic, respectively.

In particular, as Fowler points out in (PoEAA:146), a table data gateway can be implemented using stored procedures in SQL. In this scenario, every application, regardless of language, can share the same semantics for data layer access. The PHP (or other language) interface then becomes a mechanical translation of function arguments into SQL stored procedure arguments.

Finally, it bears repeating that table data gateway classes need to be stateless. Trying to write a gateway class so that one call to the class ?sets up? data to be used by a later call is a recipe for disaster. Because a table data gateway is such a simple interface, any complexity will have to come from the application. When the application tries a complex series of operations involving two or more different records, it will fail miserably if some of the operations expect to be invoked sequentially. If you need to implement a complex, stateful operation, either provide it as a single method call, or use a different pattern.

Comments are closed.