There is no rule that determines this. Mainly the object-oriented paradigm has nothing to say about it. You can come to a conclusion by adopting other concepts.

As there is no rule, everything can be right. It is possible to do both one and the other. But you can also have other options.

Concept separation option

may have a class% usuario only maintains the data structure ( DTO) and class to manipulate database access for this class. This meets the principle of cohesion and decoupling of the functions. Often this class can be called DAOUsuario since it defaults to data access objects (I do not like this kind of nomenclature). This is in line with the single responsibility principle .

Or you can further improve and implement the repository pattern . You can try to find out the difference between approaches in an existing question (I have reservations on it).

Stay alert because in the way you seem to want to do it is important to separate well what is business rule and what is access to the data itself. The above standard facilitates this. But it does not centralize everything at one point. See comment from mgibsonbr.

As the lbotinelly response, search separate concepts . This is what will really bring flexibility to the software. But there is no free lunch. Organizing brings your difficulties.

Modularize

Contrary to popular belief (justifiable), OOP encourages modularization rather than grouping of functionalities. The modularization is that it actually allowed the software to get better, more organized, and easier to maintain. And modularisation is possible in any paradigm. It's been a lot of people learning to make it better in OOP, but it's a cultural issue.

Should I put everything together in one place?

The general database connection and manipulations should be in another class.

It's rare to put everything in the database class. In fact I've never seen it in well-designed systems. This causes a high coupling and turns a class god object . This would go against one of the principles of the OO paradigm. This makes application maintenance and extensibility difficult. Object orientation was created precisely to avoid codes that were previously grouped in a single place (although they do not need this paradigm to do this). This gives a false sense of organization. This is procedural programming in its worst form.

Separating access to the database table, the entity it represents and the bank's global control would even make it easier to switch from one database manager system to another. It would facilitate formal testing.

Abstracting the storage medium

I suggest using something that abstracts the use of the database, such as the ORMs . Or evaluate if you really need to leave this open. It is very common for people to think they need this and never really use it. This probably occurs because they do not trust their decision. Of course there are cases of the real need to have other banks available in genuine generic software.

I do not particularly like this, but I understand that people benefit from it. So if you adopt an ORM, look for one that already implements the repository pattern as automatically as possible. So you'll have to worry less about abstraction and more about business rules.

In PHP people often use PDO . One of his problems, among others, is that he is only half a solution. You will still need to take care of various aspects of this abstraction. Queries will not adapt themselves because they used the PDO.

Conclusion

Does it always make up for this? No. It depends on the project, the objectives. Many people place access behavior in the entity class itself. And it works well too, but you have to know what you're doing. Obviously it makes it difficult to change the database that may never actually occur.

Of course there are still more specific questions on the subject.

Apply Separation of Interests to the maximum extent possible.

In an ideal world, your User class would not need to know how to enter a new record. In such situations, an ORM adapter is the best option: You do not need to know how the current implementation of the bank works, just using .Save() in an instance would already cause the desired effect, for example.

If you do not have an ORM layer, ask yourself: What's worse, a generic database layer knows what a user is (and therefore logical aspects of all other classes scattered throughout your application) or a user class know how to emit SQL expressions?

My choice to contain damage and compartmental scopes would be the second option.

If you really want to represent queries by classes [1], consider that "inserting a user into the database" is a concept, but the realization of this concept depends on which DBMS is being used. This could therefore be modeled using the Abstract Factory .

In this case, each model of your domain could have an interface containing the various methods that will take care of its persistence, and this interface may have N implementations (1 for standard SQL and other for specific ones). The class that represents your particular DBMS would be the factory of these implementations. At first, assuming standard SQL is sufficient to handle a model, these factories would only need to return the default implementation. But whenever a particular model needs customizations, this factory would return a custom implementation.

An example (in pseudocode) with two models and three DBMSs would be:

/* Modelos */
class Usuario
    username
    password

class Produto
    nome
    categoria

...

/* Persistência */
interface PersistenciaUsuario
    inserir(username, password): Usuario
    obter(username): Usuario
    remover(username): boolean
    verificar_senha(username, password): boolean
    listar(): Usuario[]

interface PersistenciaProduto
    inserir(nome, categoria): Produto
    obter(id): Produto
    pesquisar(nome): Produto[]
    remover(id): boolean
    listar(): Produto[]
    listar(categoria): Produto[]

...

/* Fábrica abstrata */
interface Fabrica:
    obter_persistencia_usuario(): PersistenciaUsuario
    obter_persistencia_produto(): PersistenciaProduto
    ...

A standard implementation would use simple SQL:

class PUsuarioSQL implements PersistenciaUsuario
    sql_engine
    PUsuarioSQL(engine) { sql_engine = engine; }
    inserir(username, password) { ... }
    obter(username) { ... }
    ...

class PProdutoSQL implements PersistenciaProduto
    sql_engine
    PProdutoSQL(engine) { sql_engine = engine; }
    inserir(nome, categoria) { ... }
    obter(id) { ... }
    ...

abstract class FabricaSQL implements Fabrica
    sql_engine
    obter_persistencia_usuario() { return new PUsuarioSQL(engine); }
    obter_persistencia_produto() { return new PProdutoSQL(engine); }
    ...

A concrete implementation would only customize what you needed:

class PUsuarioMySQL extends PUsuarioSQL
    inserir(username, password) { ... }
    verificar_senha(username, password) { ... }
    /* Reaproveitou parte da implementação de PersistenciaUsuario */

class FabricaMySQL extends FabricaSQL
    FabricaMySQL() { super(...); }
    obter_persistencia_usuario() { return new PUsuarioMySQL(engine); }
    /* Reaproveitou toda a implementação de PersistenciaProduto */

class PProdutoPostgres extends PProduto
    pesquisar(nome): Produto[]
    /* Reaproveitou parte da implementação de PersistenciaProduto */

class FabricaPostgres extends FabricaSQL
    FabricaPostgres() { super(...); }
    obter_persistencia_produto() { return new PProdutoPostgres(engine); }
    /* Reaproveitou toda a implementação de PersistenciaUsuario */

class FabricaMongo implements Fabrica
    /* Aqui não dá pra reaproveitar nada, pois não é baseado em SQL... */

Some comments:

• If you have M models and N SGBDs, there will potentially be MxN classes responsible for persistence;
  • If a new template has appeared, you will have to create up to N new classes; if a new DBMS has appeared, you will have to create up to M new classes.
  • Note that this schema does not necessarily obey the principle open / closed - because any change in the Fabrica interface would require changes in specific N implementations.
• The persistence of each model is neither in the model itself (which would "tie" the same to a specific implementation, and would compromise separation of responsibilities ) and neither in the class that connects to the DBMS (which would create a "god object " ). It is in a separate class, with a homogeneous interface (ie same interface for any DBMS).
  • The consequence is that the code that needs to act on the model (insert, query, delete, etc) will be the same regardless of the persistence medium used. All this consumer code needs is to get an instance of Fabrica and call the relevant methods.
• Concrete implementations can take advantage of similarities, just writing new code for really different cases. In the example above, FabricaMySQL took advantage of the entire default implementation of PersistenciaProduto , and still took full advantage of the default implementation of PersistenciaUsuario . That is, only what is different even is that it needs to be reimplementated by the final classes, everything that can be reused is reused.

[1]: I would not do this, even if I ran away from the OO template. Instead I would use a lighter medium, for example a resource file where each query is a simple string (parameterized, of course). But a good ORM could be even better.

A basic principle of enhancement in Object Guidance is the separation of responsibilities.

See Disclaimer for more details.

Four responsibilities

A widely used design pattern identifies at least 4 responsibilities in solving this problem:

1) Entity

Object that represents the entity; in this case, a system user. It has the attributes and business behaviors of the entity.

2) Repository

Object that abstracts entity persistence and recovery.

It has CRUD methods, if needed, and other search methods.

Example:

class UsuarioRepo
    Usuario[] usuariosAtivos()

The repository can also be generic, where a single object abstracts the persistence and recovery of all system entity types:

class Repositorio
    T[] obtem<T>(CriteriosPesquisa)

But the Repository does not hold database structure information, nor does it know the specificities of the database being used. In other words, the repository does not know anything about the tables in the database and does not know how to do SQL commands.

To do your job, the repository uses the following objects (and after we talk about them, we'll go back to the repository).

3) Database Entity Mapping

Based on definitions in XML files or in metadata declared in the entity class, this object delivers a mapping between entities and tables and columns of the database.

This object has knowledge of the bank structure and its relationship to entities, but it also does not know how to do SQL commands.

4) Interaction with the database

Here we have one or more objects specialized in assembling SQL commands.

There can be one object or a set of objects handling the specificities of each supported database server.

There may also be an abstraction of this interaction with the database, especially if more than one database is supported.

Back to the Repository

As I said, the repository uses the other objects to do its job. For example:

class UsuarioRepo
    Usuario[] usuariosAtivos() 
    {
        var mapeamentoUsuario = mapeamento.get<Usuario>()
        var sql = sqlBuilder.select(mapeamentoUsuario).criterio("ATIVO = 'S'")
        return conexao.executeSelect<Usuario>(sql)
    }

See that I've abstracted other responsibilities in this response, such as the connection object and objects that execute commands against the database.

ORM

If you implement this solution, you will have developed an ORM framework , but you can also use a market framework.

The ORMs for Java, Ruby (on Rails *) and .Net are very simple to use and although complex, they do not add complexity to the project because this complexity is encapsulated within them.

Using an ORM, the code looks something like this:

@Entity("TAB_USUARIO")
class Usuario
    @Column("LOGIN")
    username
    @Column("SENHA")
    password      

class UsuarioRepo
   Usuario[] usuariosAtivos() 
   {
     return orm.query<Usuario>("select u from Usuario u where u.ativo = true").result()
   }

Note that this command is not database SQL native code, nor is it about tables, but about entity names according to their class. The ORM, in turn, handled the mapping, the specificities of the bank in question, connection pooling, etc.

There is still the option of using lambda and other patterns instead of writing the command as a string; and .NET still offers LINQ.

You can still choose not to encapsulate persistence and recovery in repositories , but instead write the queries each time you need them - as are queries against entities and not against native SQL against tables, you do not would be spreading database code by your application. I usually use repositories because of some features of my context.

Conclusion

Separation of responsibilities can go far beyond writing SQL in a class other than the entity class.

You should make this separation according to the characteristics of your context.

For example, if software meets a complex need, separating responsibilities well can help prevent domain complexity from contaminating the code. The more complex the domain, the more it compensates for the separation of responsibilities, which helps to keep the code simpler (although this statement may be somewhat counter-intuitive).

Finally, you generally will not need to develop an ORM to help with separation of responsibilities as there are good frameworks available for that.