DELETE is one of the easist ways to screw up a database that doesn't have proper RI declared. Clients all the time ask to "delete" Customer X because they no longer work with them, business closed, got bought, or something.
Months later, they want to re-run reports for years past, and can't understand why the numbers don't tie out.
Nearly all the entities in my apps have an Active/Inactive flag so they no longer show up on dropdowns and picklists, but still exist in the database for historical purposes.
If there's a big enough demand, there's an admin function to remove "unused" records, but usually that's just a cleanup issue for the DBA.
As Bill said, RI rules in the DB should just be a backup for database integrity, and especially important if DBs can be accessed from other apps (like Excel!). I'm a fan of "Forbid" rules that prevent deletion of records "in use," but not "delete cascade" that makes little problems into big ones; I think a delete cascade should be a business object procedure, not one at the DB level.
And of course, as the consultant says, "It depends."
On Wed, Nov 1, 2017 at 3:54 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
Interesting discussion - considering a situation here at my job just today/yesterday.
An email from a manager claiming that since this one employee is no longer with the co. (although the email was late since the guy left about 4 weeks ago) - was asking to have this User removed from these various systems. So - I responded yesterday that I had deleted the user record from this one system.
Then - this buddy of mine, another programmer here - he was out yesterday, so he asks me about the deletion of the record. He was worried that the deletion of the record would cause problems if there are FK constraints! I told him he need not worry - since for the most part in the system I did it - there really are mostly no FK's in there. Of course, I know there are a couple - but, nothing that would be impacted by the removal of a user from a table.
So - yes - I can relate.
-K-
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, November 1, 2017 3:39 PM To: profoxtech@leafe.com Subject: Re: How many of you use foreign key constraints and References in your database schema?
On 2017-11-01 15:21, Bill Anderson wrote:
Mike,
Where's your preference in design with these in mind?<<
My philosophy is that the back end database, whatever it is, should be a dumb as a rock. A database should only be concerned with its internal integrity and no more. Anything else is pretty much geared toward vendor lock-in.
But having noted that, FK constraints are absolutely a part of the data integrity. So yes, use 'em.
Views? Sure, that's basically the only way to communicate. Business rules? Nope, stay away. That's for middleware.
Bill Anderson
Hi Bill!
I agree completely.
--Mike
[excessive quoting removed by server]