Duplicate domain constraints in table

Question

Hi

I'm designing a database to hold my domain objects. I have various validation rules on the objects such as string length and if it must be filled. These things are checked in the C# code. Does it make sense to duplicate this rules in the database, such as setting nvarchar(150) instead of nvarchar(max) and setting nullable to false for fields I know must be entered?

I don't think so, because the data should already be validated when storing them in the database and changes to the requirements must be maintained two places instead of just one.

Please share your thoughts.

Answer 1

We have struggled with the same issue. We've basically concluded that if the only way data in your database can get modified is via your application, then putting checks such as you mentioned in the database isn't really necessary. However, if users will be permitted to directly access the database and make modifications, validation would need to exist in the database as well.

Answer 2

Definitely set your data types correctly -- nullable, size, etc are all worth setting, because they'll ensure your data really is right and may be more efficient. It's a fairly 'cheap' thing to do, too; it doen't take any more effort to write varchar(150) or varchar(max)

Generating more complex rules -- that a string must match a regex, or that a number has to be in a certain range -- might not be worth it, unless you can generate your c# validation functions and the table constraints from a single source description, like Microsoft Oslo. The benefit of having a single system is that you can possibly generate validation at all levels; in a database, in C# server code, and in Javascript web client code, if necessary. That's only going to pay off on complex databases, though.

Answer 3

How long is it going to be before a second and third application is written that also writes to the same database? If you are sure the answer is "never gonna happen", then just leave the validation in the app. If not, you're better off putting the validation in the database. In some cases, putting the validation in both places is no big deal.

Answer 4

Among other things, it's an opportunity for you to express assumptions that your code would like to make about any entities it reads back from the database. As others have said, if there's any possibility that anything (another person, another (later) system) is going to make changes to this data, not via your application, then you'd best steer them towards doing the right thing. So mark required columns as NOT NULL. If you don't want the description column to contain a treatise on 19th century architecture, mark it varchar(150), not varchar(max), etc.

Answer 5

I would place simple rules in the database, rules that can easily be enforced by natural constraints by the database, such as NULL/NOT NULL, foreign key constraints, etc.

But I would keep complex rules in the domain layer in the C# code, e.g. rules like (if field A is filled out, field B must be blank).

But in respect to string lengths, I would definately set a proper string length in the database definition, but for a different reason: it gives a better performance. AFAIK an nvarchar(max) is not actually stored physically with all the other data in the same table, and the SQL server therefore has to read from two different physical locations on disc when retriving the data.

Answer 6

You can never be really sure that your application will be the only way through which updates will be done.

Which is why constraint enforcement belongs in the DBMS.

If you want to avoid "having to do maintenance in two distinct places", then leave the constraint enforcement to the DBMS and don't duplicate it in the application code.