Obsessive Compulsive Data Access Object

I've been thinking lately about how, as coders, we seem to compulsively reach for DAOs when we want a code layer over the database. A while back I used a CommandPattern on a toy web site, and liked how it worked. Each action against the DB, whether updates or queries, is a separate class. It seemed to do a good job of obeying the SingleResponsibilityPrinciple, especially when compared to DAOs with dozens of methods. But does it scale? Has anyone tried this on a large project? What patterns other than DAO or command have people tried, and how did it go? -- DonBranson

---

Litle & Co (http://www.litle.com) runs a credit card processing network, and the architecture is Java over a database server. We've been doing Agile at a large scale for over ten years now, and our developers write their own schema changes and migrates (rather than having the DBAs keep the schema under lock and key). Besides user requirements, we have to comply with auditing and security regulations from both the FTC and the credit card companies themselves. That should give you an idea of the scale of our system.

We use DAOs with multiple methods, and lots of them. DAOs with multiple methods (all tied together with a common theme, at least) isn't just something that is scalable, but I don't think that we know how to be scalable without those DAOs. -- RobMandeville

---

To clarify what I'm asking. Everywhere I've worked (as far as I remember), we reflexively reach for the DAO pattern when we want to interact with the persistence layer. I've used DAOs on big projects and small, and they work fine, but have pluses and minuses. The two minuses I usually see are 1) implemented but unused method to keep a DAO in conformance with some spec, and 2) very large DAOs. Better application of the DAO pattern would probably alleviate both issues, but it got me thinking about alternative patterns at this layer. I selected the CommandPattern for use on a sample project and it works pretty well. Each command (say, UserLoginCommand?) "does one thing and does it well" in keeping with SRP.

The question then in my mind is would the CommandPattern work well for large projects?

Sidebar on scalability - scalability exists in multiple dimensions. A system might scale well for number of users, or amount of data, or number of queries. A system might scale well in more than one dimension, but not in others. For example, a genomics system might be able to handle large amounts of data, but only for a small number of users.

For the sake of this question, the dimension of scalability I'm wondering about is the size of the codebase. In other words, Command worked well on my small project, but what if it were used for a project that grew to 200K lines of code? 2M lines of code? What would happen? Would the command approach break in that scenario, becoming cumbersome and unwieldy, or would it retain its elegance as the codebase grew?

That's one question. The other question is, "What patterns have people tried?" DAO is widely used and I think "we" have a pretty good understanding of its characteristics. I'm curious what other things people have tried, and what the results were. -- DonBranson