Stored Procedure Vs App

[lotharious]

I have two questions. First, Im basically new to stored procedures having only had experience with informix 8 years ago and dont remember much.

Im using php web app and mysql5

First question.
In a database, I will need to make an update that will be updating 4 or possibly 5 tables base on an update of the one table. The update may be a deletion of a row and not a update command. I am assuming this would be better done in a stored procedure. True or not?

If true, second question,
How do I do an if statement in stored procedures and/or any links/faq's available on mysql stored procedures?

 

[johnwm]

1. Yes

2.

http://dev.mysql.com/doc/refman/5.1/en/stored-procedures.html

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[ingresman]

1. No (well yes a bit)
I don't like stored procedures where logic is put in them for a variety of reasons the largest being you are stealing processer timee from the database server to do work that would be better of on an app server (e.g. a cluster of web servers). Database servers are very difficult to scale without using clustering technoliges which bring a whole world of pain on it's own !
As you add more and more procedures (and it's in an attractive thing to do) the db server works harder and harder until it needs an upgrade, usually an expensive new box rather than a new chepa web server.
Architecture wise they are a good and bad idea. If you have logic in the proc it has to be replicated if you move db server (more for software houses really) e.g. from mysql to Oracle or SQLServer . If the logic is complex it might be diffuclt and time consuming to do. Some databases don't support stored procs so if you moved to access (for some bizarre reason) you wouldn't be able to do it at all.
Stored procs are good to act as a marshalling point into the database by only letting them do the CRUD stuff (Create, Read,Update,Delete) on behalf of the code in your business logic. It at the very least hides the physical world of tables to app hackers.
Combining business logic in the app/web server and stored procs for actual work should give you a scaleable and (more) secure app.

 

[lotharious]

Thank you both.

As for the logic, it would be just one if statement.
In which case it does a delete instead of an update, then updates all the other tables. 99.9% of everything will be in the code. The biggest thing I am working for is to be able to make a windows app later that will work the same way as a the web app and in both cases I dont really want to send multiple queries to the database when I can put them in a stored procedure.

 

[ingresman]

It's always horses for courses on any system. Interesting you are looking to implement as a rich cliebnt as well as a web app. Have a look at some stuff on SOA (Service Orientated Atchitectures) or a good bok is Martin Fowlers paterns of enteprise architectue which has some good stuff on seperation of layers and code resue.

 

[DonQuichote]

First question.
In a database, I will need to make an update that will be updating 4 or possibly 5 tables base on an update of the one table. The update may be a deletion of a row and not a update command. I am assuming this would be better done in a stored procedure. True or not?

There is no single right answer here.
First, if this action is needed to remain data integrity, it may be sensible to do this on the database server instead of on the clients. Even then, a trigger may be more appropriate for this.

I once worked in a company that did every database action through stored procedures, for security reasons. They denied the non-admin users all rights to the data, and only granted them execute rights on the actions they needed. This is a lot of work, but I can see some sense in it.



+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)