Many years ago I took the approach of using Table classes for db table manipulations. This allowed me to use the built-in methods to generate most SQL statements and to use Omnis list and row variables to bind data to the UX classes. To handle complex relationships between tables, and to provide a place for hand crafted SQL statements, I use Object classes. This is, in my mind, a reasonable use of simple classes within more sophisticated classes. It affords a bit of mix and match, and hopefully less code overall.
This structure made simple the trapping of error conditions and deriving a class hierarchy to reduce code redundancy at elementary and higher levels. This design allows me to craft UX elements independent of data manipulation and to share code, although that advantage is not unique to my approach; others achieve the same result.
This does come at a price. As Rod and Richard pointed out, the Table + Schema classes can be many and keeping them in sync with the data model can be a bit of work. In my case, the data model is modest; 67 tables, 55 views, 16 functions and a passel of triggers and constraints. The results is roughly 100 Table and Schema classes in the application.
I have found introducing programmers to this design takes a little bit of time. To review the code without introduction often leaves one scratching his head. A number of actions that happen deep in the hierarchy need to be presented to get the idea. So while I am satisfied with the design for some reasons, I cannot say I reached my academic goal of simplicity.
