I've observed in my consider your experience that most people avoid using physical associations in tables plus they attempt to remember them and apply them through coding only.

Here 'Physical Associations' make reference to Primary Key, Foreign Key, Check constraints, etc.

While creating a database, people attempt to normalize the database in writing and things recorded. Like, if I must produce a database for any marketing company, I'll attempt to understand its needs. For instance, what fields are mandatory, what fields will contain only (a or b or c) etc.

When everything are obvious, then how come most people scared of the restrictions?

  1. Don't they would like to manage things?
  2. Have they got deficiencies in understanding (that we don't believe is really)?
  3. Could they be not confident about future problems?
  4. Could it be a real tough job controlling each one of these organizations?

What's the reason inside your opinion?

I usually possess the DBMS enforce both primary key and foreign key constraints I frequently add check constraints too. So far as I'm concerned, the information is simply too vital that you risk inaccurate data being saved.

Should you think about the database as a number of saved true logical propositions, you will find that when the database consists of an incorrect proposition - a mistake - you'll be able to argue to the conclusion you would like. Given an incorrect premise, any conclusion holds true.

How about we others use PK and FK constraints, etc?

Some are not aware of the importance (so insufficient understanding is certainly an issue, even a significant component). Other medication is scared that they'll cost an excessive amount of in performance, failing to remember that certain error that needs to be fixed may easily consume constantly saved by not getting the DBMS perform the checking for you personally. I go ahead and take view when the present DBMS can't handle them well, it may be (most likely is) time to adjust DBMS.

Many designers will look into the constraints in code over the database before they really visit perform a surgical procedure. Sometimes, this really is driven by consumer experience factors (we don't wish to present options / choices to customers that can not be saved towards the database). In some cases, it might be driven through the discomfort connected with performing an argument, identifying why it unsuccessful, after which taking corrective action. Many people would consider code more maintainable whether it did the check upfront, together with other business logic that could be at play, instead of taking corrective action with an exception handler. (Not too this really is always a perfect type of thinking, but it's a prevalent one.) Regardless, if you're doing the check prior to giving the statement, and never particularly conscious to the fact that the database could easily get touched by programs / customers who aren't arriving using your integrity-enforcing code, then you definitely might conclude that database constraints are unnecessary, particularly with the performance hit that may be incurred using their use. Also, if you're checking integrity within the application code over the database, one might contemplate it a breach of DRY (Don't Repeat Yourself) to implement realistically equivalent inspections within the database itself. The 2 manifestations of integrity rules (individuals in database constraints and individuals in application code over the database) could in principle become out-of-sync otherwise handled carefully.

Also, I'd not discount option 2, that lots of designers aren't well versed about database constraints, too readily.

Well, I am talking about, everybody is titled to their personal opinion and development strategy I guess, however in my humble opinion this type of person probably wrong :)

The main reason, however, someone may decide to avoid constraints is efficiency. Not because constraints are slow, but because storing redundant data (i.e. caching) is an extremely efficient way of accelerating (well, staying away from) an costly calculation. It is really an acceptable approach, when implemented correctly (i.e. the cache is up-to-date a normalOrsuitable times, generally I actually do this having a trigger).

Regarding the motivation not to us FKs with no caching motivation, I can not imagine it. Possibly they goal to become 'flexible' within their DB structure. If that's the case, fine, however avoid using a relational DB, since it is pointless. Non-relational DBs (OO dbs) certainly get their place, and might perhaps be superior (quite arguable, but interesting to argue) but it is an error to utilize a relational DB and never use it's core qualities.