5 Things Developers Must Know for Working with their Databases

By: Tim DelChiaro

Abstract: 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.

Hide image
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 has interactive tool tips containing business definitions, metadata and security alerts which improves accuracy, knowledge and compliance. 

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.Hide image
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.

    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.Hide image
Try Rapid SQL

    Conclusion

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:

Hide image
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 received his Master of Science from the University of Southern California and a Bachelors of Engineering from Gujarat University. Additionally, he holds many Microsoft certificates. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.

His passion for the community drives him to share his training and knowledge. Before joining Microsoft he was awarded the Microsoft Most Valuable Professional (MVP) in SQL Server Technology for three continuous years for his outstanding community service and evangelizing SQL Server technology. He was also awarded the Community Impact Award – Individual Contributor.

   

Server Response from: ETNASC02