August 21, 2013 at 5:43 pm
RonKyle (8/21/2013)
I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic
I think I can help with wondering why it's not in wider practice--this is not a good idea. The database is a chokepoint, and you want it processing as few business rules as possible. The enforcement of business rules really belongs in the middle tier, whatever that might be for the application. Let the code in the intermediate levels do their thing and then send the result to the database.
What, so you think we should maybe have tables that conform to 1NF, but normalisation to 2NF or higher is always a mistake, because only business rules can tell us what dependencies there are between different parts of the data and normalisation is all about ensuring that those dependencies are enforced by the database, and enforcing business rules in the database is anathema?
I thought that that attitude to business logic had become utterly unpopular about 40 years ago and no-one held it today, but you are showing me that I was absolutely wrong about that.
Tom
August 21, 2013 at 9:25 pm
RonKyle (8/21/2013)
As a VB Programmer, I developed a lot of processes using disconnected recordsets. I used stored procedures to retrieve the initial sets. However, the additions and changes made by the recordset object required direct access to the tables. For that reason alone, I couldn't have used views. I've seen this technique recommended before, but personally have found very little use for views in an OLTP environment. I've used them to some extent in an OLAP environment to allow me to pull a subset or higher level from a dimension table (e.g. create a quarter hour view from a dimension table that has all the minutes, a month view from a date dimension that has days or a sub list of inspectors from technicians). In one of the Kimball books I read recently views were recommended for all the tables so that the auditing information that was of no use to the end users could be shielded. This is also intriguing, but you can't set the referetial integrity among the views in the same manner as tables.
I too got a lot of mileage out of recordsets. One thing I will say is that while the default behavior of the recordsets was to go for direct access to the tables, it's actually very straightforward to switch them over to using nothing but stored procedures. Sure it adds a few minutes to the dev process - but I thought the extra work was worth every penny. The extra abstraction saved my bacon a few times, and allowed us to make changes on the DB side (such as - intercepting and validating certain changes) without redeploying the program.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 21, 2013 at 11:23 pm
I am a Computer Science Uni graduate, working as an in-house programmer for a mid-sized business, using MS-Access as a front end, and SQL Server as the database backend. Putting aside the general IT cultural bias of loving to hate MS-Access, this combination often results in may real world / economical compromises and shortcuts as mentioned in the original post.
Long ago when I stared out I inherited a few MS-Access frontend apps with mdb backends. I cleaned them up, rewrote code, added indexes (there were none), added caching, etc.. when it became time to migrate to SQL Server, while I generally knew what I was doing, I still did my googling/research, read other peoples tips and various 'best practices' and thought about how I wanted to structure my programs and data, what problems I had already had, and how I may avoid them in the future, and how to best leverage the features and power of the SQL Server.
I read the tip about always doing Views on Tables, then linking to the View from the Front-End. This just made obvious sense to me ( me a lowly VBA code monkey 🙂 and I have always, always been doing it ever since. This layer of 'independence' between my front-end app and the actual data tables has allowed me to do some very nifty on the fly conversions, data cleanups, feature migration, and backward compatibility for users that dont need to or forget to upgrade, while pushing out new versions to my power user beta testers and then onto most regular users. I suppose the technique is like a poor-mans AppServer, it gains me some of the advantages of a 3-tier architecture, even though I am using a 2-tier/Client-Sever approach.
Basically the 'View of Tables' technique has has saved me time, struggle and effort, over and over again. I have not found performance to be a problem, as the View is almost always just a straight mapping (or very simple query manipulation) onto a Table. Most of my more complicated queries have ever only involved 3 or 4 tables, and these 'complicated' queries are Front-End data manipulation that usually only need small sub-sets of the tables data, and not SQL Server Views on Tables as a layer of independence.
Whenever I have coded a front-end query that ended up being slow, I have had many other techniques to fall back on (eg Local caching of just the relevant sub-set of data in the front end app, then running my query on the local cached data, Pass Thru Queries that could even use the real sql server table and not the views, Stored Procedures, Creating a static cached result set from a View/Query each night for slow running View/Queries that are based on large but infrequently changing tables)
Admittedly I work with smaller databases, and so do not really tax a decent SQL Server, nor have to face the issues that a DBA with large databases and transactional load may routinely face, but still I read your original post and was suprized to think that everyone was not already using this technique. Mr Codd who laided the foundations for relational databases back in the early 70's wrote of using Views for these reasons and more way back then.
I dont think I have ever regretted nor questioned my use of SQL Server Views-On-Tables.
Oh, and yes I would love the dbViewReader and dbViewWriter feature. I just assumed that there was a way to force a View to be read only (which is a slightly different thing to User security Role Membership) but I just had never got around to finding it
- Matt
August 22, 2013 at 3:33 am
I've got an irrational hatred of views on tables from sorting out various endemic performance issues with systems which were essentially not very well designed. I acknowledge it was the bad design bit that was the problem but still harbour a strong dislike of excessive view deployment (of course views can be very helpful in some circumstances).
As to stored procedures, I find they quickly get overly long and impenetrable, and the business logic they contain can often be better (more clearly and elegantly) expressed in code e.g through EF. This is obviously for the types of systems I have written and with my knowledge set and experience of the other developers I have worked with and I accept it's not gospel. Clearly this does not preclude the use of procs where they are the best choice for the job.
August 22, 2013 at 6:59 am
One thing I will say is that while the default behavior of the recordsets was to go for direct access to the tables, it's actually very straightforward to switch them over to using nothing but stored procedures. Sure it adds a few minutes to the dev process - but I thought the extra work was worth every penny. The extra abstraction saved my bacon a few times, and allowed us to make changes on the DB side (such as - intercepting and validating certain changes) without redeploying the program.
If I had found a process that only added a few minutes, I would have been glad to use it. I did a lot of reading on the subject and never came across a good solution for it. I'm not sure how helpful it would be now in the .NET world (I was programming in VB6), but if it's a relavant topic for today, I'd be interested to see a sample solution.
August 22, 2013 at 7:02 am
no-one held it today, but you are showing me that I was absolutely wrong about that.
Well, then I'm glad. However, I didn't create that. As at least one other has pointed out, most business logic is more logically stated in code. Before I was taught to move the business logic to the middle tier, I remember writing some long and convoluted stored procs. When I learned that that wasn't the best way and rewrote the more complicated ones so that the logic was in the code, and the stored procedures just did the reading and the writing, not only were the rules easier to understand, but the database performance improved.
August 22, 2013 at 7:06 am
The database can be a choke point but it isn't always.
The application may be performing poorly, but it's not a chokepoint in the sense that the database is. In most setups, all the read and write requests coming from either all the applications or from the multiple servers end up going to the ONE database. That's was makes it the chokepoint. In a sense, it is the center of that application's universe. (I'm not sure I like the analogy, but for now I'll go with it).
August 22, 2013 at 7:19 am
RonKyle (8/22/2013)
The database can be a choke point but it isn't always.
The application may be performing poorly, but it's not a chokepoint in the sense that the database is. In most setups, all the read and write requests coming from either all the applications or from the multiple servers end up going to the ONE database. That's was makes it the chokepoint. In a sense, it is the center of that application's universe. (I'm not sure I like the analogy, but for now I'll go with it).
In my specific case, it looks like the application is iterating one-by-one through the records in the database just to display them to the screen. I can get the same information in a second if I query the production database directly. Even if I'm the only one in the system, it shouldn't take a full minute for the application to retrieve 22 records from the database, when it's instantaneous querying the database directly.
To keep with your space analogy, troubleshooting data issues through the application is travelling sub-light speeds, while querying the database myself (without the application) is like stepping through a wormhole to my destination.
August 22, 2013 at 7:23 am
I love abstraction, but I only use it when I get to the place where it's needed to solve the problem. I've seen the view for everything approach and it never seemed like it made much difference, still wind up with changes that change things!
August 22, 2013 at 8:09 am
Andy Warren (8/22/2013)
I love abstraction, but I only use it when I get to the place where it's needed to solve the problem. I've seen the view for everything approach and it never seemed like it made much difference, still wind up with changes that change things!
I've found that abstraction via views is most useful in a situation where the database is accessed by a several different applications or a lot of ad-hoc users. I can't trust everyone to write efficient SQL, joining tables the right way to produce correct and efficient results, so I write a manageable number of views or stored procedures to do it for them based on case usage requirements or requests. Of course, the app developers don't write the views, because that would defeat the purpose.
For a data mart that is accessed only by pre-built SSIS packages and Analysis Services, not tyically queried in an ad-hoc fashion by users, I don't see so much need for views. Preferably the end users don't even have a login there.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 8:14 am
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?
Rod
August 22, 2013 at 8:42 am
RonKyle (8/22/2013)
no-one held it today, but you are showing me that I was absolutely wrong about that.
Well, then I'm glad. However, I didn't create that. As at least one other has pointed out, most business logic is more logically stated in code. Before I was taught to move the business logic to the middle tier, I remember writing some long and convoluted stored procs. When I learned that that wasn't the best way and rewrote the more complicated ones so that the logic was in the code, and the stored procedures just did the reading and the writing, not only were the rules easier to understand, but the database performance improved.
You didn't answer my question: if you believe that business logic in the database is a bad thing, do you forbid it altogether and refuse to permit any normalisation to 2NF or higher? If not, why are you saying "business logic in the database" is a bad thing instead of "business logic expressed as imperative procedural code in SQL is a bad thing" which is actually a tenable position, unlike the one you have stated which is utterly untenable.
I have heard things like "we knew we didn't need 2NF because business logic doesn't belong in the DB, and it's easy to avoid update anomalies by coding to eliminate them in C++ (or VB or JScript or whatever language was in vogue)", seen the disastrous results, and had to rescue the company from the resultant mess; so I am going to challenge the "no business logic in the database" every time I see it, just as I will challenge so-called engineers who insist that the Nyquist-Shannon sampling theorem show that, for example, you can't get more that 16 kilobits per second through a channel with only 8kHz bandwidth (there are fewer of those around now than there used to be, fortunately) - because the two statements are equally silly.
edit: I quite like the approach someone suggested above: the business logic generally splits quite clearly into presentation logic, data logic, and other logic: presentation logic should be done in the front end, data logic in the database, and other logic in neither of those places. That division puts most business outside the database, but puts the business logic which belongs in the database in the database. It probably won't be hard and fast, though, because deciding which kind of logic something is isn't always easy.
Tom
August 22, 2013 at 9:18 am
below86 (8/21/2013)
Craig-315134 (8/21/2013)
Tom, I agree strongly with you. I favour moving as much application business logic into stored procedures as is possible,As a developer I have to disagree. 🙁 Where or how do you determine what database to put your store procdure in? Do you just designate one? I know we have a database called 'work_db' the SQL may read or write to tables on the database but it may also read from 2 or more other databases maybe even write to 2 or more.
And as I stated earlied how do you look at a log for a stored procedure? If I have a 1000 line SQL with 50 different SQL statements and it fails in the middle of the night how do I determine it failed on line 50 or 500?
Implementing business logic using a middle tier compenent, web service, or even a stored procedure solves the problem of data validation for applications, but not for ETL.
One problem I've seen in the past is where a database contains date/time values in a varchar column. Of course the proper thing to do is actually type the column as Date or DateTime, but sometimes we have to work with the legacy database that was handed to us. The application or middle tier may conform to YYYYMMDD, but then something like a one-time data migration or a periodic ETL process, inserts a block of records with the column formatted as MM/DD/YYYY.
The following column constraint will not only check that a varchar value is a valid date, but will also require that it be foramtted as YYYYMMDD (due to the 112 conversion). It matters not what process attempts to insert the row.
enrollment_date varchar(30) not null
constraint ck_enrollment_date_yyyymmdd
check (enrollment_date = convert(char(8),cast(enrollment_date as datetime),112))
Yes, this will intitially result in errors being raised in the application or ETL, but I'd rather deal with fixing a single line of code once than I would fix bad data over and over again (especially if I'm not the one who's responsible for fixing code).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2013 at 9:27 am
Doctor Who 2 (8/22/2013)
I like what you wrote here, Steve, but I wonder how practical it is. However, it might just be my work environment. Having worked in a small IT shop for so many years, it does give me a somewhat stilted view of the world. Over that time I've worked with people who could easily handle the abstraction. But also over that time I've worked with people who simply cannot. Furthermore, some of them will complain bitterly to management, saying "I absolutely REQUIRE TOTAL AND COMPLETE ACCESS TO ALL TABLES AT ALL TIMES!" And management, being management, doesn't know the difference, so they order me to give access to all tables to all users, etc. Maybe if I worked in a larger environment, things would be different?
If I was a DBA in such a situation, my response would be "I absolutely REFUSE TO GIVE YOU ANY ACCESS TO ANY TABLE AT ANY TIME!" Management can then take their pick. Of course, I have no dependents and no long-term payments on anything, so I can afford to be pretty cavalier about such things. However, I simply decline to do my job in a crummy way, regardless of who orders it.
August 22, 2013 at 9:43 am
call.copse (8/22/2013)
I've got an irrational hatred of views on tables from sorting out various endemic performance issues with systems which were essentially not very well designed. I acknowledge it was the bad design bit that was the problem but still harbour a strong dislike of excessive view deployment (of course views can be very helpful in some circumstances).As to stored procedures, I find they quickly get overly long and impenetrable, and the business logic they contain can often be better (more clearly and elegantly) expressed in code e.g through EF. This is obviously for the types of systems I have written and with my knowledge set and experience of the other developers I have worked with and I accept it's not gospel. Clearly this does not preclude the use of procs where they are the best choice for the job.
Using views doesn't mean views of views. It means that you can't access a base table as a user.
I'd argue with stored procs that "it depends". Some logic makes much more sense in the db layer, and can dramatically improve performance, not to mention ensure business rules aren't violated with a new class/method/module/application. However other logic belongs in middle/front end code layers.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 31 through 45 (of 90 total)
You must be logged in to reply to this topic. Login to reply