“That ain’t a database, it’s a spreadsheet!”

From the Sayings of Phil Factor

There is a world of difference between an enterprise-level relational database and a ‘repository of persistent data’. Until you’ve had the experience of dealing with a high-volume, high-transaction database with large amounts of data, the truth of this doesn’t really hit home.

The constant friction between the relational and the object-oriented model is due to a misunderstanding. Something that works well in the small scale doesn’t necessarily cut it at the enterprise level. Programmers tend not to appreciate the importance of indexes, stored procedures, referential integrity, constraints, or even normalisation, until they have experienced an enterprise-scale database. DBAs, in turn, find it hard to repeatedly explain the reasons. The result is often a guerrilla war between the database developers and application developers.

By the same token, where a database is unlikely to become large, or have challenging performance requirements, there is a lot to be said for techniques such as Object-Relational Mapping using Entity Framework or Hibernate, and the use of XML. Anything that can reduce the labour of application development should be considered with an open mind.

But it has to be tested.

From the very first stages of designing a development project, you must test your proposed data-handling architecture against the worst buffeting that a production system can experience, with the data volumes and characteristics you can expect. The interface between the data and application layer has to withstand the stress of real data volumes. It has to withstand attempts at intrusion or malicious damage. There is no substitute for this process, in terms of uncovering any issues with the design of your application.

This week, Red Gate launches SQL Data Generator, which we hope will help a lot with this sort of work. The tool can fill a database with enough data to expose any weaknesses in the design of a database, and the application that uses it. The team have worked hard to make sure the data generated is as close to reality as possible, in order to avoid those bugs that come from developers making assumptions.

So, if you’re working with an enterprise scale database, and are tempted to introduce one of the “latest and greatest” enterprise technologies to your application – step forward Object-Relational Mapping, XML Columns, and Entity-Attribute-Value modelling, to name but three -then we strongly suggest you test your solution thoroughly, against millions of database rows and high transaction volumes. You may find it a sobering experience.

Perhaps the great divide between the object and relational cultures is there for a reason. What do you think? Add a comment to my blog, and the best contribution will receive a $50 Amazon voucher.

The winner of the voucher for their contribution to my previous “Not the right place” editorial is TadRichard.

Cheers,

Tony.