Fog Creek Software
Discussion Board

databases are dangerous - more layers needed!

I inherited a partially completed software project last year. The software developers got into a huge tangle doing something very simple. They didn't know much about SQL (or purposely didn't want to use certain kinds of SQL) and embarked on a major module which ended up incomplete. The modules purposes was trying to get the right row out of the database!

I came in realised what they were doing was silly and replaced all their code with a few methods.

Another mistake they made was to try to load a hierachial structure from the database into flat unconnected structures. As the hierarchy and relationships between the different items of information was lost, the developers ended up flapping about aimlessly. Lots of code got written. None of it worked.

I came in and simply wrote classes that had references to other classes. The hierarchial arrangement in the database was preserved. The code I wrote was natural and easy to write. It all works. When an operation has to be carried out it is delegated to the appropriate class.

Due to the fact I can do operations simply in one line of code now (i.e. a method call which starts of a chain of events which gets deferred to the appropriate classes), I have good code reuse in the project.

Everything works. Nobody is saying otherwise.

Now I get told the old way of simply loading in whole database tables into flat, unconnected structures is wonderful and if I did it that way the code would be so much better. They could change from 1 database to another and the code wouldn't have to change.

I think the manager here is just being blind. He was never exposed to the sillyness of the old approach which just collapsed into a bug ridden mess than the old developers could end up no longer developing. As a result they just left.

The code I wrote has a disconnect between the user interface and the database. I have a nice object layer in between them. The user interface just has to call 1 method to load a hierarchial strucutre and then from there calls natural high level operations and has access to properties.

Wednesday, March 17, 2004

<g> yeah, its hard having your lovely new code _abused_ instead of admired.

sounds like youve done a good job so far, but its simply not finished yet.  maybe you missed the point of their original design (not defending their original design, sounds like a mess..but maybe they _thought_ they had a good reason) or maybe this is a new requirement....either way, find out _why_ he wants to change databases, what the purpose is and then add the code to do it.
If nothing else I bet you can write some code to create flat, unconnected structures from your current structures for the specific purpose of shifting them to other databases.

there must be a better solution though, find out more about the requirements (or tell us more :) and make them work.

Fact is that well designed code means easier to add features...sounds like maybe you missed an important point during your work (happens to the best of us at times), but its never too later to add a new feature :)

Wednesday, March 17, 2004

>They could change from 1 database to another and the
>code wouldn't have to change.

I struggle with this where I work to, people seem to justify bad designs with the "We can change databases" argument.

I have never seen people changing databases for non-trivial systems. Anybody else seen this?

Wednesday, March 17, 2004

I've developed applications with a JET backend and then deployed to Oracle.  If you code your SQL carefully and neutrally, and use an abstract connection (I used ODBC), then you stand a good chance of having database portability.

That was a strength of codefusion: a sensible connection abstraction made it easy to change databases.  PHP makes you rename every method each time, for comparison.

The only bit of code I had to change was where I was doing a WHERE x IN .. and oracle at the time didn't let me have more than 255 terms, or something like that.  Trivial.

i like i
Wednesday, March 17, 2004

If you're coding in Java, just use hibernate and get ORM over and done with ;)

If you're using some other language, well... they'll catch up soon, I'm sure!

<----- exit stage left

Rhys Keepence
Wednesday, March 17, 2004

>>>PHP makes you rename every method each time

Thats one of the more annoying design errors in PHP. It is however pretty easy to wrap that stuff up and you should probably do so anyway. Also I believe a pretty cmprehensive abstraction layer is available from the PEAR project.
Sorry for going OT.

Eric Debois
Wednesday, March 17, 2004

I knew a development manager that advocated using ADO sort/filter functionality (meaning to load the entire resultset into a recordset, and then filter/sort it within ADO -- an absolutely _horrendous_ idea on anything but the tiniest database) rather than doing it on the SQL end because the ADO method is "cross database".  In the same breath this same individual would advocate highly convoluted designs under the pretense of `scalability' (though it practice it was more along the lines of 1/imagined-scalability). You probably work for the same team.

You in London, Ontario?

Wednesday, March 17, 2004

What are the requirements? Are they met? End
of story.

son of parnas
Wednesday, March 17, 2004

>You in London, Ontario?

In reply to dot's comment, I'm in London, UK.

Yes the code I wrote works fine. It fulfills the requirements as far as the user will be concerned.

Dot is right about scaleability. The same problem is here. This manager wants whole recordsets moved into memory. Quite right, the inverse of scaleability.

Wednesday, March 17, 2004

If I may ask a somewhat-related question...

I'm working on a small tool for doing some database manipulation, on the side.

As part of the process, the user would suggest different modifications, and would see the effect of their changes on the data. I don't want the changes to be propogated to the original database until after the user has commited to them.

Stand-alone in-memory ADO recordsets seemed to fit this bill. This would only work will relatively small tables though, for obvious reasons.

Is there a better way of creating a 'sandbox' for the user?

Wednesday, March 17, 2004

I'm stunned by how often people don't change databases. They put enourmous effort into vast "layering" systems to wrap the databases up and hide details away, Java on top of C++ on top of C on top of more Java... all so they can "just change" the underlying database.

And then are still running Oracle 7 because they daren't, they just daren't upgrade because they no longer know how anything works.

It doesn't matter how unsupported those old DBs get, the more code you pile on top of it, the more nervous everyone gets about changing the foundations.

Katie Lucas
Wednesday, March 17, 2004

I'm somewhat bemused by the "data access layers" that I see on here and there. Generally a Factory is used, such that no matter what the underlying database, you can always get a ADO.NET DataSet out of it.

Given the number of times that MS has moved the data access goalposts, this seems to me to be putting the cart rather forward of the horse...

Dave Hallett
Wednesday, March 17, 2004

Uh, delete "on", sorry. I meant, on various websites.

Dave Hallett
Wednesday, March 17, 2004

Well, we did switch our code control system from foxpro to SQL Server once and are going to switch to Oracle now that our new corporate masters insist on Oracle only.

We design our software to work on MDB, SQL Server and Oracle because we have customers that use all of them. Some customers start with MDB files and then when they come to there sense move over to SQL Server.

Wednesday, March 17, 2004

Changing databases (or data sources period) isn't hard if

1) You keep your data access code seperate from everything else (i.e. don't pass bits and pieces of SQL from the UI down into the data access code that gets built into a dynamic SQL query)

2) You don't make too many unreasonable assumptions about the capabilities of the database.

It's a lot easier to follow those rules and do a manual SQL/DB-interface-library conversion than to try and make a truly db-agnostic product.

Richard P
Wednesday, March 17, 2004

Dave, a good DataAccess layer will not return DataBase objects.  A DataSet is not tied to any particular database whatsoever.

I don't even use DataSets, personally.  I return a strongly-typed collection of the appropriate objects.  This habit has already paid dividends as I've been able to reuse code in areas I never thought of without any changes.  I merely had to implement a LocalFileSystemDataLayer instead of an OracleDatabaseDataLayer.

DataSet objects might be more appropriate for complete disconnected data manipulation, but it's overkill for just populating a drop-down list or looking up an employee.

Richard P
Wednesday, March 17, 2004

Here I am! Someone said they needed more lawyers?


Wednesday, March 17, 2004

Richard, I don't use DataSets either. I just find it amusing that someone out there thinks this is a problem that needs solving. Whereas in my experience, sticking with SQL Server but having to find a whole new way of getting the data into the objects, has been far more common.

Dave Hallett
Wednesday, March 17, 2004

> Here I am! Someone said they needed more lawyers?

Yes! If we tend to believe the century city, we'll need a lot of them in 2030. And it seems they won't been outsourced to Singapore by then (but biotech, by 2030, apparently will be). Ooops. I hope I did not kill this thread by saying the 'nazi' word.

Alexander Chalucov (
Wednesday, March 17, 2004

Rich, i've gone back and fourth on the dataset vs. "data objects".  I do agree that its probably better to have data objects, but i've used datasets successfully, most becaues its so easy to get a dataview and bind it to any number of web controls.  (actually, the architecture i've gone with is a factory that holds a dataset, and returns dataviews according to what you request).  I could change this to return dataobjects, but then i'd have to implement a ton of crap to allow nice intercation with datagrids and lists etc.

Wednesday, March 17, 2004

--"Ooops. I hope I did not kill this thread by saying the 'nazi' word. "--

Wasn't you. Philo was the first to mention lawyers!

Stephen Jones
Thursday, March 18, 2004

"We design our software to work on MDB, SQL Server and Oracle because we have customers that use all of them."

In this case it is clearly a design criteria that the system needs to be database agnostic, and clearly a clean isolation from the database system is paramount. This doesn't mean, however, that the rules that apply to a cross-database ISV apply to a corporate development team developing against a large enterprise data system that has the same likelihood of changing as Bush becoming a democrat.

I've been in many arguments about higly abstracted designs, and in the end they all boil down to this -- there is no universal design for all applications, and each system architecture needs to be built around the specific requirements and _realistic_ future needs of a specific project. Just because a certain design makes sense for low-value-per-transaction large scale ecommerce sites, as an example, doesn't mean that it is therefore appropriate for small high-value-per-transaction internal development (indeed the inverse is true).

Dennis Forbes
Thursday, March 18, 2004

*  Recent Topics

*  Fog Creek Home