I have read conflicting philosophies on where data validation should happen and it is just confusing me more. Some say it will simply be within the database. Others state that the validation rules ought to be shown in other layers such as the bll or ui.

Where if the data validation live? Should rules be split across multiple layers? What are the actual guidelines (instead of theory, mind within the clouds type stuff) regarding where and when to validate data within an application running on the top of the database.

it ought to be done:

  • in the point it's first joined
  • anywhere on the way it's transformed/altered
  • anywhere on the way it might cause a mistake or incorrect data

so inside a database driven web forms application, for example, you'd have client-side javascript validation, most likely some server side validation in the industry logic, after which further constraints within the database, varying from datatype to check on constraints.

My 2 cents:

Data validation should take place in 2 locations:

  1. The stage where information is behaved upon, for instance validating input parameters for an SQL query.

  2. General validation at the stage where information is posted, for instance inside a web application some validation should take place around the client. The benefit being that you could rapidly inform customers of input issues, i.e. improperly created phone number, string too lengthy etc. However this shouldn't be depended upon to become a authoritative validation check as, within the situation of the web application, a malicious user may bypass an client side validation.

For me the database shouldn't be carrying out general validation, data ought to be validated/steered clear of/sanitised prior to it going in to the database. Nevertheless your database schema can provide you with an amount of abstract validation through column data types, constraints etc. Nevertheless, data that may trigger difficulties with these ought to be 'cleaned' prior to it being passed in to the database.

This stated, you will find many wrong ways but there's no proper way. Validation is dependent around the architecture of the application, the character from the data within inside it and just how that information is used.