I came across a system where most of the field binding rules are done in the application and not in the database (with the exception of the primary key).
As I have no experience with many systems, I would like to know if this is common and brings some advantage or if it is impractical.
It would be best to first ensure the Integrity Rules (RIE and RIR) in the database itself to maintain consistency. This ensures that even if your application is changed or replaced, your data will be protected and consistent.
When it comes to business rules, many prefer to enter them in the application.
You have a very interesting post on this subject at the following link:
This is a very controversial subject, especially among the people who develop web software. Some prefer to leave everything in the application, others to share responsibility.
Personally I believe that the less "loose ends" you leave the better. Thus, I believe it is worthwhile, if its application is large enough, to do the validation of consistency at both ends.
The application takes care of its part by validating everything that is required before entering it into the database (never trust the data that users send) and the database maintains its foreign keys and correlates.
As a developer, I often break application-bank consistency when working on a project alone. And I'm sure things tend to break down more when more people work together on a project, or even when more than one project needs to access data from the same database.
Keep on both.
The thing I see most today are systems where the rules are only in the application, since currently knowing the minimum database seems to be a big requirement.
What happens? In the best of scenarios, where only your application uses the database: some user goes there directly in the DBMS and erases some data. Since there are neither foreign_keys, ready: now the data is inconsistent. And I see this with a frightening frequency.
In the worst-case scenario, multiple applications will have access to the same database.
Then, at a minimum, also keep the fields that are required (defined as NOT NULL ) foreign keys in the database, and use constraints for values such as ENUM in MySQL (or Oracle CHECK) when possible
Validation (at least mandatory fields) and constraints (such as foreign_keys) on the seat are like seat belts: you will find that you never need them until an accident happens.
Dude that will depend on your need, both business and consistency.
There will be cases where you will need to ensure in the database that the field value can not be null , I'll give you an example, you have a Product table that relates to Plan in this table Product you have the value of the product, if in the database you do not leave it as mandatory you can include a product via insert in the bank without this value, but if for example, you do this, and in the business layer of your programming language you are using it would be fatal to have this value to sell some Plan of that Product , you would start to have a very large lack of consistency in your data to the point where it is not possible to sell.
But like the vast majority of factors in the area of software development, there should always be an analysis, even when you need such concise data in DB and even when not. This will be done in the way that you conclude best for each case.
When possible, I prefer to have the guarantee of the constraints in the BD, it is not always because there are packages sold in several Bds and it may be impracticable to administer different Bds.
I also like Triggers validation of Business Rules being that the same comments already said.
