Rules like "A parent object will have up to 2 children" could be enforced in database using triggers. But would it be a good idea to duplicate the rule if this rule is already enforced in the domain layer.
In which cases duplication of such rules are justified? Are there any alternative to avoid such duplication? Is it not a data integrity rule?
Thanks
Typically I would keep this logic out of the database layer. It's sounds to me more like a business rule, which should live in a business tier. What happens if this rule were to ever change? where and in how many places do you want to update it? Typically nothing should be accessing the database except through your business tier and therefore it's OK not to implement it anywhere else BUT the busines tier. Besides, triggers are messy, hard to mantain, and most importantly, hard to debug later on.
IMHO anyway.
imho triggers are generally a 'bad thing' but beyond that, enforcing business rules at the level of inserts into database tables doesn't strike me as a good 'separation of concerns'
I would try and stay away from putting this type of 'business validation logic' in your database layer. Eventually you'll need more control and handling over your data than what triggers provide, as your application matures.
If your application has a business layer, I would only have SQL enforcing relations, and not rules.
I think every tier of a system should enforce the maximum number of constraints that it's comfortably designed to handle. The rest you let others handle. I would consider database constraints and javascript constraints to be in somewhat the same category; you do what you can with the available means while staying reasonably clean.
Triggers are far outside what I'd consider reasonably clean, so I'd let the business logic handle this. There are many data integrity rules that go way outside what you can expect an SQL database to handle.
It's virtually impossible to do this correctly in code that's outside the database. Any code that looks to see if 2 records already exist and if not then allows a new record to be added can be fooled by 2 threads operating simultaneously on the same parent. Unless you actually lock the database table or somehow serialize the child addition process, either of which create a real lack of scalability.
You don't mention the RDBMS so it's hard to give you a solution.
EDIT:
I agree with those who say that triggers are far from clean. But they are not the only way to enforce rules within a database. That is why I said without knowing your RDBMS, it would be impossible to suggest any database solution or even one that may not require a trigger.
Beside, you shouldn't need a trigger because your middle tier never does DML, it just calls database procedures or packages which encapsulate your CRUD. RIght?!
It depends.
If all writing to the database is done by a single application or service, it's reasonable to implement such business rules in the business tier of the application/service. Especially if you think the rules may change in the future.
But in many real-world scenarios, especially with legacy systems, you may have multiple writers, and it may be worth the additional complexity of implementing such rules in the database.
All design is trade-off.
I would require this to be in the database in a trigger. This is because databases can be affected by other than the GUI and rules of this nature that are not enforced at the database level result in data integrity problems.