On Mon, Sep 7, 2009 at 2:30 PM, Zberteoc <zbert...@gmail.com> wrote: > > Some consider good practice to actually never expose directly the > tables to the users but do this through stored procedures and views > only. In this way you achieves 2 major things, complete control and > security. Users have no permission to select/update/insert/delete on > the tables but they are given permission to stored procedures and > views that will do that instead. A side but important advantage is > that using a stored procedure or a view in application code is far > more cleaner and easier than a select statement that can be cumbersome > at times to write and maintain. A stored procedure also embeds the > business logic and if/when it changes you don't have to touch the > application code but only the stored procedure itself, providing that > the parameters and column names and number stays the same. Even if > they change is far more easier to maintain that then the SQL code > which they replace. Beside that stored procedure code is compiled and > cashed so they perform much better than ad-hoc queries. Unfortunately > very few apply these principles when they do database design and > development. > > With triggers is another story. In my opinion triggers should be used > only when there is NO alternative. The problems with triggers is that > they are "hidden" and they can create extremely serious problems > performance wise. Imagine an insert of million rows and with each one > some complicated code has to be executed in triggers especially if > querying some other tables is involved. You have to be careful here as > in the short term a trigger might seem the easiest and fastest > solution but in the long term you might run into serious problems like > very low performance or even locks and/or timeouts. That doesn't mean > we shouldn't use them though but as I said we have to be careful. A > very good way of avoiding triggers is using stored procedures for > insert/update/delete where you can adapt the code to perform much > better that you would do it in a trigger and still have the same > control and effect on the table. Unfortunately usually what happens is > this, web/application developers write queries (select/insert/update/ > delete statements at times very complicated and low performing because > of their limited SQL experience) which they embed in their code or, in > the happy cases, in some external files (XML) and later when they come > to change or modify the functionality will add triggers on tables just > to let applications intact. In time this practice develops into a > bloated database with poorer and poorer performance as the time goes > by. In the end they will have to revamp much or everything just to > optimize the database code which translates in more cost and wasted > time, and only because they treated superficially the database design > and development part in the first place. I've seen tables with 10 or > even more triggers on them, which in my opinion should be > "forbidden". :o) > > Cheers.
Excellent points. I've been on the user side of views as a consultant in some larger corporations, usually just for periodic data extraction, though they make things soooo much easier than moving around flat files (which was actually the preferred alternative by their sys admins!) Personally I've used views commonly when I have a recurring query to simplify my queries in code. I've stayed away from stored procedures primarily because I like to keep all of the business logic in one body of code, as opposed to split between application code and stored procedures. I can see it helpful tho when you have multiple applications accessing the same database. I also appreciate their speed. We've used them for field level encryption with fantastic results. In fact, it was the only way it was even viable. The speed hit went from unusably crippling in application code, to unperceptible in as a stored procedure! I tend to agree with you on the triggers bit. I've played around with using them to log bits of data to later synchronize with a smaller, read only copy of the same database, but they decided against the project. It probably could have been handled with stored procedures tho. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
