Is it practical to have back-end (database side) validation for everything?

Question

With Back-end validations I mean, during the- Triggers,CHECK, Procedure(Insert, Update, Delete), etc. How practical or necessary are they now, where nowadays most of these validations are handled in front-end strictly. How much of back-end validations are good for a program? Should small things be left out of back-end validations?

For example: Lets say we have an age barrier of peoples to enter data of. This can be done in back-end using Triggers or Check in the age column. It can/is also be done in front-end. So is it necessary to have a back-end validation when there is strict validation of age in the front-end?

Answer 1

This is a conceptual question. In general modern programs are built in 3 layers:

1. Presentation
2. Business Logic
3. Database

As a rule, layer 1 may elect to validate all input in a modern application, providing the user with quick feedback on possible issues (for example a JS popup saying "this is not a valid email address").

Layer 2 always has to do full validation. It's the gateway to the backend and it can check complex relational constraints. It ensures no corrupt data can enter the database, in any way, validated against the application's constraints. Those constraints are oft more complex than what you can check in a database anyway (for example a bank account number here in the Netherlands has to be either 3 to 7 numbers, or 9 or 10 and match a check digit test).

Layer 3 can do validation. If there's only one 'client' it's not a necessity per se, if there's more (especially if there are 'less trusted' users of the same database) it should definitely also be in the database. If the application is mission-critical, it's also recommended to do full validation in the database as well with triggers and constraints, just to have a double guard against bugs in the business logic. The database's job is to ensure its own integrity, not compliance to specific business rules.

There's no clear-cut answers to this one, it depends on what your application does and how important it is. In a banking application - validate on all 3 levels. In an internet forum - check only where it is needed, and serve extra users with the performance benefits.

Answer 2

This might help:

1. Front end (interface) validation is for data entry help and contexual messages. This ensures that the user has a hassle free data entry experience; and minimizes the roundtrip required for validate correctness.

2. Application level validation is for business logic validation. The values are correct, but do they make sense. This is the kind of validation do you here, and the majority of your efforts should be in this area.

3. Databases don't do any validation. They provide methods to constraint data and the scope of that should be to ensure referential integrity. Referential integrity ensures that your queries (especially cross table queries) work as expected. Just like no database server will stop you from entering 4000 in a numeric column, it also shouldn't be the place to check if age < 40 as this has no impact on the integrity of the data. However, ensuring that a row being deleted won't leave any orphans - this is referential integrity and should be enforced at the database level.