3
I came across a system where most of the mandatory field 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.
8
The best would be to first ensure the Integrity Rules (RIE and RIR) in the database itself, to maintain the consistency of the database. This ensures that even if your application is changed or overwritten, your data will be protected and consistent.
When it comes to business rules, many prefer to insert them in the application.
There is a very interesting post about this subject in the following link:
4
This is a very controversial subject, especially among people who develop software for the web. Some prefer to leave everything in the application, others share responsibility.
Personally I believe the less "loose ends" you leave better. In this way, I believe it is worthwhile, if its application is large enough, to validate the consistency at both ends.
The application takes care of the part of it validating everything that is necessary before entering in the database (never trust the data that users send) and the database maintains its foreign and correlated keys.
As a developer, I often break application-bank consistency when I work alone on a project. 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.
4
Keep in both.
The thing I see most nowadays are systems where the rules are only in the application, since currently knowing the minimum database seems to be a great requirement.
What happens? In the best case scenario, where only your application uses the database: some user goes there directly in the DBMS and erases some data. As there is no foreign_keys, ready: now the data is inconsistent. And I see this with a frightening frequency.
Worst-case scenario, multiple apps will have access to the same bank.
So, at the very least, also keep in the bank the fields that are mandatory (defined as NOT NULL), foreign keys, and use restrictions for values such as ENUM in Mysql (or Oracle CHECK) when possible.
Validation (at least mandatory fields) and constraints (like foreign_keys) on the seat are like a seat belt: you’ll think you never need them until an accident happens.
2
Face this will depend on your need for both business and consistency.
There will be cases that you will need to ensure in the database that the value of the field cannot be null, I’ll give you an example, you have a table Product 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 may 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 is of fatal need you have this value to sell some Plan that Product, you would start to have a very large lack of consistency in your data to the point that it is not possible to carry out the sale.
But like the vast majority of factors in the area of software development, there should always be an analysis, even when you need these concise data in DB and even when not. Do this implement the way you conclude best for each case.
1
When possible I prefer to have the guarantee of constraints in BD, it is not always because there are packages marketed in several Bds and it can be impossible to administer different Bds.
I also like Triggers validation of Business Rules and fits the same observations already said.
Browser other questions tagged database sql-server
You are not signed in. Login or sign up in order to post.
Speaking exclusively of field obligation I do not see why a NOT NULL is a subject of discussion.
– Motta
NOT NULLIt’s nothing to argue about. if the field is mandatory, it must be present, if it is not mandatory, the field should not even be in the same table but in another auxiliary table.– Bruno Coimbra