How do you use Table Data Gateway pattern involving one-to-many relationships?

Question

I've been trying to learn more about design patterns by reading Patterns of Enterprise Application Architecture by Martin Fowler. I came across the Table Data Gateway pattern and was wondering how do you use it if you have operations involving multiple tables?

My understanding is that every table will have its own class. Each class will have SQL statements for accessing a single table, but what happens when some of my statements rely on other tables?

Here's a concrete example. If I have a one-to-many relationship between two tables, such as Questions and Choices (multiple choice questions), and would like to retrieve a question with all its choices. Then I would have a QuestionGateway class with a method find() but would I also have a ChoiceGateway class with a method findByQuestionId() to retrieve all the choices for the question?

Answer 1

I would suggest to be a bit more flexible interpreting this pattern. You should not feel limited or take strange decisions just to be 100% compatible with patter definition.

Please don't get me wrong. I'm not suggesting to throw patterns away. What I'm actually suggesting is to use them in a proper way.

Answering your question:

• if there are not so many interdependencies between your ChoiceGateway and QuestionGateway it is normal to have them separated.
• if there are interdependencies and it is better to have those tables "joined" from the business logic perspective you can create something like a "view", call it QuiestionView for example. In such a way your business logic might be a bit more clear and all DB specific stuff will be incapsulated in the defined view.

Basically, it is all about useful abstractions. If you are comfortable working with those tables independently define independent gateways with some potential duplication. If you need "everything in one place" just define some high-level view abstraction.

BTW, the pattern itself abstract not only tables, but view. So I see no reason you can't create higher level abstraction using defined approach.

A Table Data Gateway holds all the SQL for accessing a single table or view

Answer 2

Your conception of what this pattern looks like is right on the money. Your question suggests that you also grasp the more important idea of the type of problems the pattern is meant to solve.

The Table Data Gateway pattern is not meant for complex data models where related data is stored in multiple tables. It's meant for simple data that has no relationships with any other data in your database. It's a throwback from before the days of dedicated key-value stores, when people wanted to enjoy ACID guarantees on data that had no use for a relational model. As you've noticed, the pattern degrades as soon as you care about the relationships between your data. If you imagine yourself ever wanting to JOIN this table with another, don't use this pattern. Since the entire point of a relational model is to preserve the relationships between data, in practice it is usually avoided.