5 Things Developers Must Know for Working with their Databases

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.

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.

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.

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.

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:

• Rapid SQL – The Intelligent IDE for SQL Development
• Rapid SQL free trial download 
• White Paper: Zero to Hero with SQL Server Management Studio by Pinal Dave