Infrastructure: models as an external to an application to avoid stored procedures

Question

My thoughts:

I absolutely despise stored procedures for various reasons: cost, scalability, and compatibility.

• Cost: I can get 2-3 good light weight web application servers for the cost of one good MySQL server.

• Scalability: Sure I can cache query results, but when using stored procedures I lose the opportunity for a much finer granularity of what can be cached plus it ties the applications to always using MySQL ( who's got the money to re-write the stored procedures from MySQL to something else? )

• Compatibility: at some point list_foo_widgetsByUser() stored procedure might not fit the needs of client #123. It would be suicidal to modify list_foo_widgetByUser()'s signature... so then I'd have to write a new sproc cl123_list_foo_widgetByUser() and that way leads to maddness or a homicidal DBA.

My solution:

Rip the models out of the application's repository and put them into an external repo. Every application would then have a models/Base subdirectory that was point to an external repository. Then put a simple factory method in front like GetModel("FooWidgets") that would either return the baseFooWidget class as an instance or a application specific child instance. That would allow individual applications to inherit FooWidget's class or combined with some tool like Liquabase, allow for a bigger base of variability.

A voice in the back of my head says this is too easy...what am I missing here?

References: I know for a fact that the PHP Kohana framework does something along these lines to allow application designers to wrap Kohana's base library with added functionality and if PHP can do it, I can't see any other language having a problem.

Answer 1

It's an excellent thought to get rid of stored procedures, you hit the nail exactly with your three points.

PHP, on the other hand, doesn't easily allow structured wrapping. I'm not a PHP addict (more of a C# / Java guy), but the best way to tackle this is separate database/domain/access/business layers. In short:

1. At the bottom: the database. Just tables, relations and that's it.
2. Then you need the mapping: simple objects that represent your tables. Normally one object per table.
3. Next, you need the methods to deal with these objects. Most tables will need a "get all", "get by id" and "save" methods. Ideally, these go into a separate module, so it can be developed without needing to change the mapping.
4. Finally, you need your business logic, which can go into a separate layer or in your application.

This is a simplified overview. I don't know if you meant this with your solution, but this is usually the way it goes: separation of concerns. If you change the database, you only need to change the mapping. If you need different result sets, you only change the access layer.

Tools that can help you with this process are Hibernate (but then you need JavaBridge), it's the ORM tool of choice, but has a bit of a steep learning curve. For PHP, it seems that Doctrine can do the lot for you. Other tools exist as well. Ideally, a tool allows roundtrip engineering: if you change something, you run the tool again (or change something) and you don't break the application.