While developing my custom issue management system I have made an effort to make a 3-tier model, and here it is:
- Persistence: database related code.
- Business: code related to the system’s business logic such as entity operations (Users, Issues, Projects, relationships and so on), transition control (things to do when the state of an issue changes), access control (login, logout).
- Interface: all code behind classes.
There are some project principles that I tried to follow in the model:
- Keep cohesion in classes: each class should deal with only one subject.
- One way communication between layers: the communication must occur only in a top-bottom way, assuming that the Interface is on top and the Persistence is on bottom. This means that Interface classes can use Business classes, but Business classes cannot use Interface classes. This is important because I can change the entire Interface layer without worrying about the Business and Persistence classes.
I’ve decided not to use stored procedures, but dynamic SQL instead. This decision made me think about where’s the best place to store the SQL statements: the persistence layer classes or the business classes?
My opinion is that the classes from the business layer are the best place, and this is all about “subject”. If I create a class to store the statements in the persistence layer I’ll have a BIG class with code related to the whole system, dealing with many different subjects. This is often seen as a bad property of a class, that ideally should deal with only one subject to keep it easily maintainable.
By keeping the SQL statements in the business layer I’ll put these codes in the related classes. For example, the UPDATE statement that updates an user record in the database will be placed in the save() method of the User class. By doing this, I’ll not make the User class deal with a new subject since the above SQL statement is all about users.
Igor Ramadas said
I would go with the first option, putting all the SQL statements together. If you need to support a new database or repository, that isn’t SQL compliant, you’ll have to change all your business classes. Putting them together (all code related to data access on one or more dedicated classes), you could create a base class, that has common statements, and create inherited classes with the special modifications for each database.
And you could assume that “data access” is a subject, too
ceconjr said
Ok, you’ve got a point.
However, I think that if “ease of migration to different persistence types” was a requirement, I would go with the adoption of a good persistence service implementation like NHibernate (http://www.hibernate.org/343.html).
About “data access” being a subject, I do not agree with it (that’s just my opinion). We must remember that what matter is not really the number of subjects at all, but the cohesion of a class. As we can define cohesion as the degree of relatedness of the members in a class, I think that a class with a hundred methods that contains dynamic SQL code is not dealing with one subject called “data access”, but maybe with a hundred subjects, each one related to the class that uses the method. I’ll tell you why: when you write a method in this class (the class that contains all the SQL statements), you will have a code snippet that builds a SQL statement using dynamic SQL. What is the MOST important subject here? The “data access” represented by the construction of the SQL statement or WHAT the SQL statement really do (eg: retrieves a user from database)? I stay with the last one.