The Sql Paradox

Way back in the day we wrote programs that used files, mainly flat files on disk, often on tape, usually sequential or indexed.

If we wanted to change a file layout, we just changed the program; if there was data to preserve, we had to write a one-off program to copy the old data to the new format.

When we wanted to test programs, we could redirect the input and output to different files and compare the results using a file compare utility. Several people could be testing against the same logical file by using different physical files.

Now we have SQL, which lets you change table layouts with a simple one command, usually they deal with any old data. You can replicate, snapshot and rollback to allow repeated testing.

Then we have DataBaseAdministrator?s, so that when you want to change a file you have to send an email to them with your SQL, and they will run it when they have time.

You can't have a test DB each because the DBAs can't support all those DBs. Everyone develops and tests against the same DB. You can't start your tests from the same state, you can't test the SQL you have to send to a DBA because it may screw up everyone else's testing.

This is why I'd choose Access over Oracle for anything where I could get away with its terrible scaling. -- AndyMorris

There's nothing paradoxical about SQL, only about the management traditions that have grown up around using it. Reasonably powerful boxes are cheap enough that there's little excuse for developers to not have their own development database they can play with however they like - just another form of BetterSyntacticSugar, I suppose. (Oh, I'm forgetting that hardware isn't powerful enough to waste cycles on conveniences for developers these days; I should give you a disclaimer that this is, in Constin's expert view, a ridiculous and unnecessary overhead. Consider yourself warned.)

Indeed it's not the technology itself, it's entirely the culture that goes with it. The paradoxical aspect is that despite the fact the SQL is much easier to refactor than a bunch of files (sequential and indexed), the cultural baggage that goes with it makes it much harder.

The Oracle way seems to encourage a very waterfall approach and over-specialization so that management think that developers should not have DBA skills as it's a waste of time and they might do things they shouldn't. In our shop, the DBAs have some of the air of the Stazi. -- AndyMorris

Alternative: PersonalOracle?, on your PC, with a centralized IntegrationTesting Oracle instance controlled by the DBA.

We knew we could do this on a recent project (year 2000), but lacked the guts to do it. :-(

I'd love to do that, but my place has caught the DBA meme, so that the DBAs wouldn't let it happen and management doesn't think the developers would have the DBA skills to admin their own DBs. Its not really a technical pronlem, more of a cultural thing.

[I think the title, TheSqlParadox, sucks; anyone want to change it? -- AndyMorris]

[SqlCulture?? TheTroubleWithSql?? -- FrancisHwang]

As I understand it, it's standard operating procedure for Oracle to grant unlimited PersonalOracle? licenses for use within organizations that fork over the big bucks for enterprise production Oracle servers. However, it also seems common for those organizations to forbid anyone from using PersonalOracle?, as they know they can't manage PC databases centrally, and they seem to feel that they can't trust their people to be knowledgeable and trustworthy.

Personally, if I thought that some of my employees weren't sufficiently knowledgeable and trustworthy, I'd just fire them. (...or train them! ;-) But maybe there's something wrong with me. ;-> -- JeffGrigg

PersonalOracle? is still quite combersome. What is needed is a standardized language that works in both a nimble MS-Access-like tool and BigIron RDBMS. That way, one could scale up and down and all around. Standardization is the biggest problem.

The standard answer to TheSqlParadox, as for GraphicalUserInterfaces and other tools, is that we don't expect enough of our vendors. If databases and user interfaces had been invented via TestFirstDesign, our testage would merely extend these systems.

Instead, we slowly chip rocks into wheels, one at a time... -- PhlIp

I can't imagine doing database development where everyone shares one set of tables. Each developer needs his or her own database account and tables. (A separate Oracle install is nice but not necessary.) -- BrianSlesinsky

But if the schema design is still dynamic, this could create some nasty change propagation issues. I agree that the BigIron view of RDBMS is problematic in many scenarios and wish there were more product options, such as a small-footprint DB that could be traded up to the BigIron version when needed. Related: SimplifyingRdbms

Funny, I never had this problem. Part of the install scripts made a set of databases and could fill them with test data. The names of the databases could be changed, so that I had a different set of databases than you did. This did not require DBA intervention, because development owned its own server. You had to deal with DBA and all the necessary bureaucracy when you got out of development and wanted to send a copy to QA, however... -- RobMandeville

To me, there is an emerging role in development teams: the developer/DBA, or development-support DBA. This can be a developer with DBA skills, or a DBA with development skills. They are there to keep the team agile, to ensure development instances are taken care of, and to promote/teach/coach good database programming practices in the context of the trade-offs for this particular softwaer system. If one is missing this role, I think it leads to a general "anti-database" mindset that seems a bit too common among ObjectOriented AgileMethods teams. -- StuCharlton

The DBA is like a "consistency cop". Some cops are good and some not so good. Some precincts are swamped and some are not. It would be interesting though to see how larger organizations would work with DynamicRelational where tables and columns can potentially by created at whim just by "using" them, reminiscent of associative arrays. I'm not saying it would necessarily be better, but it would change the dynamics of change-impact management.

DynamicRelational would probably be better for intermediate or custom local processing of data copied from the "master" or "enterprise" tables from a formal RDBMS for specialized uses. Specific departments often want "local" abstractions of the data that fit their view of things and only their view of things in order to simplify queries and usage for them. It's a matter of the right tool for the job. (And DynamicRelational can theoretically incrementally "lock down" requirements such as validation {types} and existence as needed.) --top

See Also: DbasGoneBad, NimbleDatabase, DynamicRelational

View edit of April 2, 2010 or FindPage with title or text search