At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather than put more rules in the backend database. The idea was that if I changed backend databases, I wouldn't need to rewrite a whole bunch of specific DBMS code.
Where's your preference in design with these in mind?
On 10/31/2017 4:26 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather
For large applications (500+ tables) I usually require it. Smaller apps... eh... I think there would still be benefits but I'm usually building them so fast that end users won't take the time to provide a complete enough picture to drive all RI rules. Sure, I could still do them on my "internal design only" tables, but like you said, there is some overhead associated. So in the smaller cases it's pretty situational as to whether or not I use it.
My attitude is, generally, the more you can put "into" the database, that "belongs" in the database, the better flexibility, maintainability, and stability you'll get.
-Charlie
On 2017-10-31 17:02, Charlie-gm wrote:
On 10/31/2017 4:26 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather
For large applications (500+ tables) I usually require it. Smaller apps... eh... I think there would still be benefits but I'm usually building them so fast that end users won't take the time to provide a complete enough picture to drive all RI rules. Sure, I could still do them on my "internal design only" tables, but like you said, there is some overhead associated. So in the smaller cases it's pretty situational as to whether or not I use it.
My attitude is, generally, the more you can put "into" the database, that "belongs" in the database, the better flexibility, maintainability, and stability you'll get.
I am using triggers in one of my apps (FabNet) to make sure keys have cascading updates/deletes elsewhere as needed.
Constraints stop some devs cold when they are trying to "clean up" their data.
It is in the correct space in the database because it protects the data there. Compliance auditors like it as well.
On Tue, Oct 31, 2017 at 3:26 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather than put more rules in the backend database. The idea was that if I changed backend databases, I wouldn't need to rewrite a whole bunch of specific DBMS code.
Where's your preference in design with these in mind?
[excessive quoting removed by server]
for a stand-alone VFP solution it'd probably not worth the trouble, for a SQL back-end // where agents other than your program might get access // I like to use pk; fk constraints; mostly deny access to the base tables and provide user views barricaded with 'instead of' constraints that validate insert / delete / update transactions with logging. The hope is that even illegitimate users can only do 'proper' transactions and at worst can be traced.
On 01-Nov-2017 1:56 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather than put more rules in the backend database. The idea was that if I changed backend databases, I wouldn't need to rewrite a whole bunch of specific DBMS code.
Where's your preference in design with these in mind?
[excessive quoting removed by server]
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
On Tue, Oct 31, 2017 at 1:26 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather than put more rules in the backend database. The idea was that if I changed backend databases, I wouldn't need to rewrite a whole bunch of specific DBMS code.
Where's your preference in design with these in mind?
[excessive quoting removed by server]
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
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]
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]
Yeah Ted - thanks for the input. Yeah - it depends. I checked with the season veteran programmer there - as she has been there for like 17 years and supported the system for which I deleted the record. I was pretty sure there was no Inactive status for the User record - and she confirmed I was right. So - in this particular instance - simply deleting that User record is no big deal. I Promise! Cross my heart!
:-)
-K-
On 11/1/2017 6:01 PM, Ted Roche wrote:
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]
Totally agree - my default would be an 'instead of Delete' trigger that set a deleted flag (and logged it), and all regular user views to filter on that flag.
On 02-Nov-2017 3:31 AM, Ted Roche wrote:
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.
<snip>
Just have another table to retain the pkey, date and reason why of all the "Remove From View" entities.
Now you can do an where NOT IN clause against this table to filter properly.
How do you manage mergers and cascading data?
Our clientele are the oligarchs in the food-oil industry. They will sell each other physical plants which are our direct trading partners.
When you want to keep the history of what that "Ship to address" has done over the past 5-10+ years what do you do?
On Thu, Nov 2, 2017 at 1:18 AM, AndyHC andy@hawthorncottage.com wrote:
Totally agree - my default would be an 'instead of Delete' trigger that set a deleted flag (and logged it), and all regular user views to filter on that flag.
On 02-Nov-2017 3:31 AM, Ted Roche wrote:
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.
<snip>
[excessive quoting removed by server]
On 2017-11-01 15:54, Kurt Wendt 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-
I always advise to disable or deactivate and remove access rather than delete completely.
I don't know about dumb as a rock. Here is a short list of stuff that a dumb rock doesn't do, IMHO.
I want the db to be able to do ETL with any data instrument I have and validate if errors are generated as I am doing it. I demand the jobs engine run a specific job when told to do so, alert me when it is done and if an error happens more about that. For my DW environment, I expect data selection and placement into the proper cube.
The email service to push out messages as sprocs define them to the correct person as well as the correct attachment.
I expect excellent security that interacts with my AD for users that come and go their rights follow the proper channel. I also expect that I can go full-on gearhead and define access to a specific object in the database if that is needed.
As limits for growth are defined they better be followed.
Logs are a necessary evil.
I expect to be able to backup my databases and restore them easily.
The api for the dataengine should be available in at least two layers for access.
I want to tell the db what schemas a user or application is allowed to use.
I expect to be able to write functions() that can do specific transformations as needed.
Output can be is dataset, xml, json, in encrypted or non-encrypted form.
I expect the database to adapt to many language sets and allow joins to other databases that are in different ones.
On Wed, Nov 1, 2017 at 2:21 PM, Bill Anderson billand88@gmail.com 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
On Tue, Oct 31, 2017 at 1:26 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
At the day gig, they use FK constraints and References in their Oracle database schema. Honestly, I got into that years ago with VFP but then kinda fell off that wagon over the years, preferring to just handle some things in the BizObj of the solution rather than put more rules in the backend database. The idea was that if I changed backend databases, I wouldn't need to rewrite a whole bunch of specific DBMS code.
Where's your preference in design with these in mind?
[excessive quoting removed by server]
-
AndyHC -
Bill Anderson -
Charlie-gm -
Kurt at VR-FX -
Kurt Wendt -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell -
Ted Roche