5 Things Developers Must Know for Working with their Databases

by Oct 14, 2014

This article by Pinal Dave looks at some of the developer traits that one must keep in mind while working with databases

Being in the databases field one of the most rewarding experience and at the same time the most demanding. As developers, we get an opportunity to improvise and become better at what we do the best, work with data. The challenge for any developer is that, they are constantly chasing behind timelines and are constantly looking at the delivery in hand.

Product companies, ISV’s and Services companies are reinventing themselves while working with databases. Be it from the conventional SQL Server, Oracle, MySQL or the latest trend of NoSQL – the world of data is challenging the developers of today. As much as these are challenging times, we personally feel the fundamentals and basics have never changed.

Many of these products are trying to get to the market, make an impact and then improvise based on what the requirement is. So there is lots to learn from experience, lots to learn from mistakes and lots to learn from each other. In this blog post, let us look at some of the developer traits that one must keep in mind while working with databases.

Rapid SQL Video

Watch the video: Rapid SQL: The Intelligent IDE for SQL Development

Do you have the right tool for Database development?

Rapid SQL works across all of your enterprise data sources, it's one environment for all of your development needs.


    1. Know your Production Environment

Developers are on a constant grill to deliver to the next release that is staring down the horizon. As simple as this cycle of get your requirements, code it, test it and release it happens. There are some components that are always in the back seat – things like security or performance. Though these are two different aspects, it is critical to know how your software is currently being deployed in the production world. Knowing and mimicking a similar environment on development servers will surely reduce a lot of heartburn, especially from a performance standpoint. If your developers are working on a database size of 5GB while the production environments are at 550GB, then developer’s badly written code will get amplified in the production servers easily. So have a near realistic database size for development and testing environments.
Zero to Hero White Paper

    2. Normalization techniques

Though the world of normalization has been there and most Database Architects tend to use the 3NF without a doubt in their designs. This is surely a time tested scenario and recommendation, but it fails at one point. We highly recommend to understand how this system will be used in your customer side – is it for Transactional workloads, MIS, Reporting, Analytics, Social, Predictive etc. Knowing your workload decides if we need to denormalize or do special materialized views for reporting needs. There is no one size fits all – but there is always a design that fits your requirement.

    3. Indexes and Query pattern

Indexing is an art – irrespective of which DB world you come from. The basics don’t change while the specific implementations can change between database vendors. Indexes are great ways to optimize database performance. Overdoing it can take you for a ride in the opposite direction too. There are no rules of thumb to the number of indexes – your query pattern, access mechanism and workloads decide how many are needed inside a database. Though there are tons of tools available, we caution you to do it on a case-by-case basis. A developer’s current workload based on development server may not be representative to the data distribution on production environment. So build basic indexes during software deployment and later tweak the same in production.

    4. Integration systems

When we deliver products like an ISV or a Services industry, we work in silo as part of our product. Our customer’s environment are heterogeneous in nature and it is important to respect the same. So whenever working on specific customer implementations, know your integration points – either data comes from one system and how data moves to another system. There is no magic to how this might happen. Being a mediator we need to keep both the sides happy. For example, the downstream app that gives data might be missing some fields that we need to add before we pass the data upstream to another application. Knowing these nuances helps the bigger cause of building a robust application.
Try Rapid SQL

    5. Baseline based on testing

Just like we mentioned Security, testing is a non-negotiable task. In the world of automation, it is imperative that we do our weekly (if not daily) builds of our work in progress and run a bunch of automated scripts to make sure there are no regression bugs that have got introduced in our systems. Each developer is expected to write their unit test cases and as part of their check-in report out the number of test they did with code coverage. In this era of Githubs, TFS and many other source control systems – this is something every developer needs to get agile about. The future is here.


Developers are super heroes in this era of software development. They are the architects for best software one prepares. The challenges in deliver puts them into pressure that management fails to understand. If empowered with more responsibility and right direction, developers of tomorrow can be powerful, efficient, organized and above all the best resource that any company can have. Processes and few tweaks in the system can make developers productive and they will understand the bigger picture while they develop some world class products.

Click to learn more about Embarcadero database tools related to this post and more from Pinal Dave:

Pinal Dave

About Pinal Dave – Technology Evangelist & Founder of SQL Authority

Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at http://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.