2

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.

Templar
  • 1,843
  • 7
  • 29
  • 42
rogret
  • 21
  • 2
  • 1
    just because some hacks messed up the SQL doesn't mean that a rewrite using SQL isn't the way to go. You seem to want to use a non-relational DB, but don't give any real reason why, other than the original coders used a bad design. – KM. Jan 28 '10 at 21:42
  • I didn't voice a blue-sky goal in my description: 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). In an ideal world, these transformations could be understood by a non-SQL guru, would embody the business rules, and could be compiled into some processable byte-code. True, the current DB design leaves a lot to be desired. Probably most DBs that grew through accretion and from Access/FoxPro are that way. – rogret Jan 29 '10 at 14:30
  • 1
    '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'... You pretty much just defined SQL. – Matthew Wood Jan 29 '10 at 23:15
  • 1
    You give no hint about the actual shape of the data. Is the problem poor database design and execution? Or is the problem that the data is not, inherently, relational? The best choice of tools and methodology derive from the nature of the problem, not the nature of the tools. – Larry Lustig Sep 26 '11 at 01:41

2 Answers2

1

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.

bkm
  • 973
  • 7
  • 13
  • I agree that Referential Integrity could be added assuming that there are no data conflicts and that this shouldn't negatively affect performance. In fact it may actually enhance it since many tables don't have primary keys. Having a well linked set of tables would also make it easier to use a DAL or ORM as the DB schema would be able to describe the relationships in a better way. I think that taking the time to do a full DB decomposition and rewrite would be well merited - I'm just not sure I have the stomach for jumping into so much code. – rogret Jan 29 '10 at 14:20
  • Many tables don't have primary keys? If the original "designers" omitted primary keys, what are the odds they indexed the tables correctly? – Mike Sherrill 'Cat Recall' Sep 26 '11 at 02:41
  • "adding referential integrity constraints will not affect the performance in any way" -- not necessarily true: such constraints will prevent certain bad data from entering the database in the first place. If instead every query must eliminate the same bad data 'on the fly' then that will have a negative effect on performance. – onedaywhen Sep 26 '11 at 08:34
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.

Community
  • 1
  • 1
tolitius
  • 22,149
  • 6
  • 70
  • 81