August 23, 2013 at 12:10 pm
Craig-315134 (8/23/2013)
@Jeff Moden wrote:Too many people get to most databases without using the application.
???
Please explain, Jeff. You've thrown me. If people aren't using an application UI, what are they using?
Basically SSIS, BULK COPY, INSERT/UPDATE, etc. that violate foreign key constraints or duplicate keys. Not only do these bypass the application and middle tier, they tend to be manual and error prone.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 23, 2013 at 12:24 pm
RonKyle (8/23/2013)
It's not my intention to write a book to explain every detail and nuance. The forum is for an exchange of general ideas. Almost anything anyone says can be taken to an extreme and made to look like it doesn't make sense. I could take your preference to have the business rules in the stored procedures and create thousand line stored procedures, but I assume you're not advocating that. (You're not, right?) There's a latin phrase for that which escapes me. However, I will look at your details over the weekend.
Trouble is that you appear to want an absolutist position that there's no business logic in the database, even if you have to redefine the term "business logic" to exclude quite a large chunk of what the business people (as opposed to the IT people) call business logic. I offered you what I thought might be an acceptable and less absolutist phrasing a few posts back: "business logic expressed as imperative procedural code in SQL is a bad thing", but that wasn't good enough. And after I have offered that, you suggest that you need to assume that I don't want to have 1000 line stored procedures, instead of accepting that you've been told it in pretty plain language. I don't really know where to go from there.
Tom
August 23, 2013 at 12:28 pm
Trouble is that you appear to want an absolutist position that there's no business logic in the database, even if you have to redefine the term "business logic" to exclude quite a large chunk of what the business people (as opposed to the IT people) call business logic. I offered you what I thought might be an acceptable and less absolutist phrasing a few posts back: "business logic expressed as imperative procedural code in SQL is a bad thing", but that wasn't good enough. And after I have offered that, you suggest that you need to assume that I don't want to have 1000 line stored procedures, instead of accepting that you've been told it in pretty plain language. I don't really know where to go from there.
I told you that I would look over your comments more carefully over the weekend. There's just not time during the work day to carefully review and give a thoughtful response. And I'm anything but an absolutist. Ultimately I'm about solving practical business problems, where time permits in the most effective manner possible.
August 23, 2013 at 12:31 pm
Don't forget ORM tools. Some of the complexity of what needs to be done either means complexity is pushed into the application or implemented using triggers.
August 23, 2013 at 1:11 pm
Craig-315134 (8/23/2013)
The solution to the problem of misusing a specialised tool is not avoidance. The solution is in education and training. Developers, as many of us have witnessed, often 'MacGyver' application design through the incorrect and injudicous use of triggers. Very well, then, let us educate them.(They are educable, are they not? 😉 )
Well, yes, almost all developers are educable, and it usually isn't difficult to educate them.:-) Most DBAs can be educated too, but some of them are very difficult to educate because they've been taught so many "facts" on introductory DBA courses or read them on the web or heard them from other DBAs :angry: and they think that anyone who challenges those "facts" is wrong. Four examples: Normalisation is a bad thing because it causes a massive increase in database size, it's far more efficient to leave the tables un-normalised and fix the anomalies with triggers instead; A trigger is a less expensive and error prone way of implementing a domain constraint than including a check constraint in the schema; Cascade actions on foreign constraints are dangerous and error-prone, so any tidy-up needed should be done in a trigger instead; A trigger is always required to update through a view. Believe it or not, all four of those pieces of nonsense have been taught in DBA training courses, and so have plenty more.
So yes, let us educate them but let us also recognise that education can take time and forbid our developers and DBAs the use of triggers until their education in this respect has reached a reasonable level.
Actually, MacGyvering is sometimes the best thing to do; for example if I can fix a broken power cable with three one inch pins and some duct tape, that will do until the cable can be replaced (*). Of course I would rather there was a new cable in the spares closet, but if there isn't there isn't. However I find it difficult to understand what MacGyvering might be in connection with a database.
(*) actually I can't legally do that any more in the UK - Chartered Electrical Engineers are no longer permitted to do work like that because they are not qualified
edit: edited to add footnote.
Tom
August 23, 2013 at 1:24 pm
Craig-315134 (8/23/2013)
@Eric M Russell wrote:Basically SSIS, BULK COPY, INSERT/UPDATE, etc. that violate foreign key constraints or duplicate keys. Not only do these bypass the application and middle tier, they tend to be manual and error prone.
(ROFL).
Not my business users!
Not mine either, but developers write "data fix" scripts, DBAs write SSIS packages for migrating data from legacy or 3rd party databses, etc. The application is not necessarily the only point of entry for insert/update/delete.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 23, 2013 at 1:30 pm
Tom wrote:
Most DBAs can be educated too, but some of them are very difficult to educate because they've been taught so many "facts" on introductory DBA courses or read them on the web or heard them from other DBAs and they think that anyone who challenges those "facts" is wrong.
I think many professions, including app development and data base administration, have their 'legends', or 'myths', or whatever one may wish to call such fallacies. It is understandable human nature, and such fallacies are not easy to eliminate; earth, air, wind and fire persisted, unquestioned, as the four classical elements from before Socrates into early modern times.
Actually, MacGyvering is sometimes the best thing to do ...
'Quick and dirty' today often means 'protracted correction' tomorrow. Of course sometimes there are resource constraints - time, money, staff - but I am always amused there there never seems enough time to do something the right way from the start, but always enough time to do it over again.
A bad start usually means a bad finish.
... I find it difficult to understand what MacGyvering might be in connection with a database.
Oh, my, let me count the ways! For starters, misusing/overusing triggers (in lieu of writing and documenting procedural code); omitting (on the DDL side) the correct assignment of data types to columns (thereby letting the application, and the application developer, assume the data typing); the lack of referential integrity constraints (once again being defaulted to the application and application developer) ... there are many more (and worse) horrors, but these are a few I've encountered of late.
Shortcuts make for long delays.
August 23, 2013 at 1:36 pm
Eric M Russell wrote:
... developers write "data fix" scripts, DBAs write SSIS packages for migrating data from legacy or 3rd party databses, etc. The application is not necessarily the only point of entry for insert/update/delete.
Well, of course they do, certainly (one of my biggest and earliest cock-ups as a developer was using IEBPTPCH, which I discovered could completely hose large quantities of data very quickly!) But as an application development manager, these non-application data interfaces are not my bailiwick (thankfully).
August 23, 2013 at 3:18 pm
Craig-315134 (8/23/2013)
@Jeff Moden wrote:Too many people get to most databases without using the application.
???
Please explain, Jeff. You've thrown me. If people aren't using an application UI, what are they using?
This is a part of what I'm talking about. Too many people think that the only way in or out of a database is through the UI (whatever it is).
For example, we have a large number of batch processes that bring in mountains of customer-provided data auto-magically and the processes don't go anywhere near a UI, not even something like SSIS. We also have "project masters" that necessarily do their data tweaks directly on the data staging database through SSMS. Technically speaking, there is no "presentation layer" and there is no "business layer" for those folks.
Most companies have similar situations and you have to impart business and, sometimes, presentation logic to the database using only the database.
Yes, we have UI/GUI interfaces for the apps that look at the data when it finally makes it to the production database but, as I explained in one of my previous posts on this thread, I don't necessarily observe the boundaries implied by others in the "definition" of the presentation, business, and data layers because it doesn't work all the time. It's a guideline with a huge number of practical exceptions, like the ones that I've previously posted and more.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2013 at 3:24 pm
@jeff Moden wrote:
This is a part of what I'm talking about. Too many people think that the only way in or out of a database is through the UI (whatever it is).
Um, well, I'm not one of them. Please see my previous post.
As an application development manager, I concern myself primarily with the functional needs of the company's business users. Answering those needs involves developing business applications and related UIs (whatever those are 😉 ).
And so I don't go putting SSIS, or BULK INSERT SQL statements, or anything of that sort into the users' hands. I don't even allow my development staff to play with data manipulation tools like those; our trusty DBAs handle that.
August 23, 2013 at 3:34 pm
Craig-315134 (8/23/2013)
Seriously, though, Jeff, I don't think 'data safety and performance' and application layer abstraction must be mutually exclusive goals;
I never said they needed to have mutually exclusive goals. In fact, I'm implying that they're all just flavors of the same thing and that you shouldn't allow perceptions of what (for example) people think a business layer is to keep you from protecting the data in the tables by imparting business logic in the database, especially within the constraints of the tables themselves.
Trust me: there very much is such a thing as application layer abstraction, and correctly done it contributes not only to application maintainability and code reuse, but to data security and database performance as well.
Like I said before, I agree with application layer abstraction for all of the reasons you've stated but people also have to remember that the UI/GUI actually makes up only a very small portion of the larger functionality for many of us.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2013 at 4:09 pm
Craig-315134 (8/23/2013)
@Jeff Moden wrote:This is a part of what I'm talking about. Too many people think that the only way in or out of a database is through the UI (whatever it is).
Um, well, I'm not one of them. Please see my previous post.
As an application development manager, I concern myself primarily with the functional needs of the company's business users. Answering those needs involves developing business applications and related UIs (whatever those are 😉 ).
And so I don't go putting SSIS, or BULK INSERT SQL statements, or anything of that sort into the users' hands. I don't even allow my development staff to play with data manipulation tools like those; our trusty DBAs handle that.
Good. Then we agree. 😀 And, no... our "project masters" aren't just business users. They've been trained for the job they do and I have the same trust in them and what they do as I have in an excellent DBA.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 90 total)
You must be logged in to reply to this topic. Login to reply