Our company standards require we use store procedures. The procedures provide a window to the database and are meant to hide the complexity from the programmer. They don't even get to see the tables. They provide better performance, and you have people who understand the data writing the queries.
The database is meant to hold data, not business rules. Keep them in your code. Let the database maintain data integrity.
If your concern is to be able to use a different database down the road, then use a data abstraction layer to hold all your code that accesses the database. Use a factory to provide you with the data abstraction object you need. You could have an Oracle and MS SQL implementation of your data abstraction layer. Your web programmer never has to care which is being used. A simple change in a property file can tell the factory which to provide.
This approach requires some division of labor, and depends on having the resources available. It also takes more time to develop it right. However, if done right it can save a lot of headaches in the future.
Slashdot Mirror
Our company standards require we use store procedures. The procedures provide a window to the database and are meant to hide the complexity from the programmer. They don't even get to see the tables. They provide better performance, and you have people who understand the data writing the queries. The database is meant to hold data, not business rules. Keep them in your code. Let the database maintain data integrity. If your concern is to be able to use a different database down the road, then use a data abstraction layer to hold all your code that accesses the database. Use a factory to provide you with the data abstraction object you need. You could have an Oracle and MS SQL implementation of your data abstraction layer. Your web programmer never has to care which is being used. A simple change in a property file can tell the factory which to provide. This approach requires some division of labor, and depends on having the resources available. It also takes more time to develop it right. However, if done right it can save a lot of headaches in the future.