Development in three layers

Question

In a system developed in three layers, I must leave some consistency for the database or I must solve everything in the middle layer?

Example: I have a user table, where the name should be unique.

id=1
nome=João

id=2
nome=Carlos

id=3
nome=João 

When sending the user id=3 the system cannot accept the inclusion of the user, but I must leave this to the database, which has a unique index for the name field, or I must resolve this in the middle layer, looking for if the user already exists and if yes return a message to the user?

Answer 1

The term "3-layer development" is too generic to define anything. Even if he did define it would be just a rule to be followed without looking at the context of what he is actually doing. Rule applied without context does not serve for much, arrives to disturb.

Repeated data is common to let the database inform because it is the most suitable to know if there really is a repeat in all situations. In some it is even possible to leave for another instrument to do it, but there are no guarantees. It has to do right anywhere.

I often see the programmer check if it exists and then decide what to do. This is checking the application. The correct is to have the database do what you want (in this case the inclusion) and see if it failed because the registered user already exists. Then if it fails, you can do whatever you want, including sending a message to the user.

It is important not to confuse this with an auxiliary query, before insertion, just to avoid the user unnecessarily filling in a registration, for example. But this is already a UX problem.

This applies to any type of development involving a database, using any technology, technique or methodology.

Answer 2

Both ways are valid in 3-layer development.

_{Just aligning the concept of three layers (despite being a classical, ancient and consolidated definition, one may not be familiar): the layers are Interface/Presentation, Applying and Database. The history of this definition and function of each layer, if necessary, can be context to another question.}

In addition to these two ways (validate either on the application layer or on the database layer), there is also a third option that is validate in both layers.

I explain: depending on the tools you are using, it can be difficult to show a friendly message the user in case the duplicity is rejected only by the database.

Database systems often show messages of the type:

"SQL error 3344: attempt to violate the single index Ix_aaaxxxbb09222_01..."

and rarely these messages are useful to the end user.

Even if you try to give a meaningful name to each index or Constraint, the message can still get very strange - besides that friendly names are not always possible by limitation of the database itself (name size, use of special characters, etc.).

So when to use each of the three options:

Validation only in the database

You can only validate in the database when the chances of breach of constraints are remote or when the Constraint breach error does not affect the common user interface (when the error can only occur in integration scenarios, for example).

If feasible, according to the tools you use, you can also validate only in the database and rely on a routine that translates error messages properly, showing a more user-friendly message to the end user.

Validation only in the application

You can only validate in the application when the application itself is able to ensure integrity at a satisfactory level.

If respect to constraints is the premise of the system, validating only in the application can be a high risk too much to assume, besides the benefits being few because the database validates constraints with more performance than the application.

The most common scenario for not having constraints in the database is the need for high performance, and in these cases the system is modeled not to depend on the guarantee of relational integrity or uniqueness of records.

In-app and database validation

This option is useful to not let the user go too far in his work when even before starting this job no longer has any condition to be persisted.

There may be specific business rules that are worth the effort to check beforehand whether user entries are valid for the database, and warn you in advance that it needs to go another way.

Then, of course, in the act of persistence, the bank’s constraints still come into play to ensure integrity.

Finalizing

It is common for the same system to adopt the three options, validating only in the database, or only in the application (or simply not using constraints) or validating in both layers, depending on each requirement.