sql - Should validation be carried out at the application or database level? -

-

i relatively new database programming , find coding aspect of sql counter intuitive. coding mean, example, finding single string value in column , capitalizing it, concatenating value, modifying based on table references it, etc.

my question whether better enforce rules governing data @ database level or @ application level, , why. this article (see enforcing integrity via applications), example, argues should enforced @ database level.

here 2 examples, 1 obvious, 1 more complex.

if have simple person table so

create table person(     id serial,     age int check (age>=0) );

it makes sense enforce non negative age @ database level. however, if have more complicated example so

create table person(     id serial,     age int check (age>=0),     spouse int references person (id)  );

ensuring

  1. both spouses on 18
  2. both spouses single @ time of marriage
  3. spouses not in polygamous relationship

requires more complex triggers. alternatively, these checked when adding database far more human readable languages php or python. if database built 1 program, acceptable enforce these rules @ database level?

this crucial design question when designing database , application. things think about:

  1. are planning give users field level validation feedback? if so, field level validation might better done in application code. otherwise, when attempt insert have decode exception figure out problem is.
  2. how familiar database , capabilities? more logic put database, more dependent on syntax , behavior.
  3. will application ever ported database? example, sql-server mysql? or postgres oracle? if so, consider amount of effort needed translate logic have embedded in schema new database's lexicon.

i tend put validation application , integrity constraints (a.k.a foreign key constraints) database. put validation application can tell user what's wrong input. think integrity constraints vital @ db level because, inevitably, need make sort of manual database update correct problem, add new capability, or whatever. constraints protect against errors in manual updates , against bugs in application logic.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more