Validation: do it in the class, or in the database?

Question

I'm playing with LINQ-To-SQL in .NET 3.5 and have built some classes based on an underlying database. I now face the decision: do I validate data in the class code, or in the database?

I can see pros and cons either way. If I do validation in the database, the validation takes place no matter what app is using the database. (I currently don't think that any other app will ever access this database, but who knows.)

OTOH, the class provides a much more robust programming environment to do my validation. Certainly, it allows me to catch the problem while the UI is in a state where the user could correct the problem, rather than waiting until the item is submitted to the database. The Linq-to-SQL can capture datatypes for columns, but doesn't (AFAIK) provide programmatic enforcement of any CHECK or DEFAULT constraints in the underlying database.

So, for instance, a "Last Modified" property of the entity: I could update that using an ON INSERT trigger in the database, or I could do it in respone to the PropertyChanged event in the class itself (having a class subscribe to its own event sounds odd, but since the class is in two files defined as partial classes, and one of those files is autogenerated by the IDE, doing it this way means I don't have to tamper with the autogenerated file, so it won't break if I have to regenerate the .dbml), or I could do it in the app when the entity is submitted to the database.

I don't want to duplicate validation in the database and in the class. I like the robust environment of validating in the class code. But the purist in me thinks that validating in the database ensures consistency even if another app (which, again, I don't think will ever happen) uses the database.

What do others think of this topic?

Answer 1

I'd do it in the class. IMAO, the UI benefits thoroughly trump any concerns about a hypothetical application bypassing your API.

Answer 2

It's usually better to catch validation errors as soon as possible and show them to your user. Sometimes it means you have to validate on client side already to save server round trips. BUT. Your DAO objects probably have some semantics of their own. Which means you'll have to validate them as well (or you'll get DB exceptions if you have certain constraints on your schema).

Answer 3

IMHO, validation is best done at multiple levels. I do not see a problem with performing a different type of validation in the UI layer, Business logic layer and lastly the Database itself.

In my mind, the ideal scenario would be one where the UI layer validates user submitted data for possible malicious input, illegal values, Regex pattern matches and required fields. The second layer would validate the type of data and whether it meshes together to form concrete updatable objects that can be propagated to the data store. The database would impose basic constraints such as the one you mentioned for all operations.

Also, the level of validation required would decrease with each level... starting from rigorous in the UI layer to basic in the database layer.

I believe in one rule - "All input is evil until proven otherwise."

Answer 4

I don't view this as an either/or proposition. Different kinds of validation are more appropriate in different areas. Typically I handle content validation in my data layer -- field lengths, null/not null, formats, values, etc. -- and relational validation -- foreign keys, uniqueness, etc. -- in the database. Generally, I would say take the pragmatic approach and do the validation where it makes the most sense and/or is easily accomplished.

Note that I will also do as much validation client-side as is easily possible. In a web app, I'd prefer to not get a post back unless I'm reasonably certain that it contains valid data -- for the user's sake. This doesn't remove the need to do validation on the server, but it can make for a better user experience since the user doesn't have to wait for a request cycle to learn that they missed a field.

Answer 5

As stated elsewhere: Do the validation at all levels. Unfortunately, this is more work. However, you really want to protect your database from errant adhoc poking or later application programs.

Having said that, consider making some kind of "rules" code generator that can read your validation description, and generate code appropriate for each context. Better yet, search around to see if one is available for the DB / middle / client stack you are using.

I imagine doing something like this:

• Have one or more"rules" files.
• Have templates for the the target code files (SQL, java, C#, javascipt, whatever) with a "macro" hook which will be replaced with the generated code.
• Have a make / ant / maven / rake / whatever target to generate the code as part of your build process. (obviously, this assumes you are not in a "point and drool" build environment -- I use IDEs at times, but I really hate to rely on them to do builds)

Well, that's my blue sky take on it anyway.