Timeline for Why are constraints applied in the database rather than the code?
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 26, 2017 at 21:33 | comment | added | FrustratedWithFormsDesigner | ... I guess if that's the case then I might agree. It would probably depend on the application and on how complicated the rules could get. If it was a situation with multiple client applications of different versions, then yes, different rules in different client versions could definitely be a motivator to move rules into the database. I was probably thinking in the context of a web application where there will only be one application instance, talking to one database. You do raise a good point, though. :) | |
| Jan 26, 2017 at 21:28 | comment | added | FrustratedWithFormsDesigner | @EvanCarroll: Re-reading my example carefully... I did actually mention a colours table that would contain a list of colours. The constraint that I said could come from outside the database was of a particular product type only being allowed to associate with a particular colour. Other products might be allowed to reference other products, the decision of what is permitted is what I said should be in the application. I'm thinking that you would prefer the rules of what products may have what colours should also be in the database, so implement a rules engine in the database too? | |
| Jan 26, 2017 at 17:25 | comment | added | Evan Carroll | @FrustratedWithFormsDesigner certainly, it's actually the poster child for a foreign key constraint. Assume you have three clients of different versions/builds of the db access point, what are you going to do when you stop shipping that product in red? Where are you going to store the list of possible color combinations? Hint: I've got a centralized place for you. And if you create the table color_products, and color, you'll likely be able to create the additional drop downs with more ease -- most IDEs/schema loaders, support following fkeys. | |
| Jan 26, 2017 at 15:11 | comment | added | FrustratedWithFormsDesigner | @EvanCarroll: So you think the product colour example constraint should be enforced at the database level? Why? | |
| Jan 26, 2017 at 5:00 | comment | added | Evan Carroll | I downvoted this. There is no time when a constraint is best enforced in the application only. The tone of this answer is weighted improperly. | |
| Apr 12, 2013 at 23:13 | vote | accept | hkoosha | ||
| Apr 12, 2013 at 20:09 | comment | added | FrustratedWithFormsDesigner | @loolooyyyy: Another way to use the original data model and still let teachers be students might be to have another table called teachers_as_students which is another subtype of Students and has a new foreign key refering to Teachers, and a system-generated primary key, instead of a Social Security Number. This way, a "student" actually is an alias for a teacher so the teacher can still register to take a class. It's hard to say for sure how well this would work without seeing the whole data model. | |
| Apr 12, 2013 at 20:03 | comment | added | FrustratedWithFormsDesigner | @loolooyyyy: Yes, I think that's correct. If the database enforces the first rule (that a person can only be a student or an employee) then the situation you described (in which an employee wants to register for a class) is impossible because: the person cannot be both, and it's not even possible to create a second "person" record because they can't share Social Security Numbers which are presumably issued from a third party (such as the government). Of course, this overly restrictive data model might work for some cases... | |
| Apr 12, 2013 at 19:39 | comment | added | hkoosha | So according to this answer, The rule <a person can only exist in Student's sub-type table or only in Employees sub-type table> should be applied in code, And Database has <The Student/Employee sub-type must be a valid person> constraint. Am I right? (It was book's example). thanks. | |
| Apr 12, 2013 at 17:24 | history | edited | FrustratedWithFormsDesigner | CC BY-SA 3.0 | added 4 characters in body |
| Apr 12, 2013 at 15:37 | history | answered | FrustratedWithFormsDesigner | CC BY-SA 3.0 |