Replace relational DB (SQL Server) with rules-based/declarative implementation?

Question

I have started working on a project in the financial services industry that is based (mainly) on SQL Server (2000), ColdFusion (8), and some Access/.NET applications. This project started as some simple Access forms/VBA and was slowly converted to web interfaces.

I could say that the database design and application coding was done by people that were learning on the job and didn't have the opportunity to learn about good design principles from the start. Many of the business rules are set in a myriad of cascading functions and stored procedures as well as in the web server templates. There is a huge amount of special case handling deep within complex 500-line SQL UDFs that use uncommented constants. It is very difficult to trace all of the interactions between the 10-20 UDFs that might be involved in a query. Some of the queries seem to take way too long to run (up to 15 minutes).

While the tables are fairly well indexed, there is a lack of FK relationships and almost no referential integrity. The DB is updated infrequently with daily batches of low volume (1,000 records in multiple tables.) It is primarily used to serve as a data repository - I suppose a data warehouse. We get very infrequent deadlocks or delays.

So, my question is: If I want to re-implement the whole project including the database and front-end would it make sense to look at non-relational implementations? The primary DB is only about 1GB (.mdf) so it could fit easily in memory. I would like to move from the SQL query structure to some declarative model that could be efficiently compiled and executed. If necessary, I could use the SQL DB just as a data store.

Answer 1

• If I want to re-implement the whole project including the database and front-end would it make sense to look at non-relational implementations?

In general, most of the developers, even those who breathe map/reduce, and wear NoSQL T shirts, feel a LOT more comfortable with SQL.

If your application follows the classic MVC/MVP model, then most of the frameworks ( e.g. Spring, Rails, Grails, Django, Webmachine, etc.. ) actually come with first class support for a SQL back end. And some support for a NoSQL one.

In case you see no actual benefit that NoSQL can bring to your system ( here are the benefits I posted to another question ), why bother?

• I would like to have a set of "english-language" rules that describe the transformations from the underlying raw data to a form that can be directly consumed by the application (web)

Seems that you are talking about a classic persistence layer with a service layer on top of it. Where "english-language" rules are just "english-language" methods in your service layer. Unless you need a more sophisticated rules engine, but most of the time it is not needed.

Answer 2

Why do you want to move from the relational approach? By moving from the relational approach you are only going to bury business logic deeper into the code by using any other approach. As you pointed out, the data model is fairly simple. You could first look at improving the data model itself. The reason they may not be any referential integrity constraints is because the initial designers might have assumed that this would lead to lower performance. They might be doing the checks using code that might itself be inefficient.

Your DB is small. adding referential integrity constraints will not affect the performance in any way. If required, you can rewrite some of the UDFs. Why dont you use a query analyzer to look at the performance metrics? That will give you a good starting point for analysis.