The term "3-tier development" is too generic to define anything. Even if he defined it would be just a rule to follow without looking at the context of what he is actually doing. Rule applied without context is not good for a lot, it even gets in the way.

Repeated data it is common to let the database inform you why it is best suited to know if there is actually a repeat in all situations. In some it is even possible to let another instrument do this, but there are no guarantees. But it has to do right anywhere.

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

Important not to confuse this with an auxiliary query, prior to insertion, just to prevent the user from unnecessarily filling a record, for example. But this is already a UX problem.

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

Both ways are valid in 3-tier development.

Just aligning the three-tiered concept (although it's a classic, old, consolidated definition, someone might not be familiar): layers are Interface / Presentation , Application and Database . The history of this definition and function of each each layer, if necessary, can be context for another question.

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

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

Database systems often display messages of the type:

  

"SQL error 3344: single index violation attempt IX_AAAxxxbb09222_01 ..."

and rarely are these messages useful to the end user.

Even though you try to give a meaningful name to each index or constraint, the message may still get very strange - plus friendly names are not always possible by limiting the database itself (name size, use of special characters , etc.).

So when to use each of the three options:

Database-only validation

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

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

Application-only validation

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

If compliance with constraints is a system premise, validating only in the application can be a high risk to assume, and benefits are few as the database validates constraints with more performance than the application.

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

Application and database validation

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

There may be specific business rules that are worth the effort to check in advance if the user's entries are valid for the database, and warn him in advance that he needs to follow another path.

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

Finishing

It is common for a single 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.