This new series by leading MySQL Expert Ronald Bradford helps the software engineer understand, appreciate and develop the right skills and techniques to build scalable software solutions. These proven and reproducible design practices will ensure your use of MySQL to improve performance, scalability and reliability.

These expert design practices are from 25 years of professional experience following formal university qualifications in computer science. All of these practices are written for use with a MySQL based data system however most of the content in these practices predate the existence of the MySQL product and have stood the test of time with emerging technologies and software development approaches. Many practices apply directly to other data stores, whether relational or the new NoSQL products and include working with persistent and non-persistent data storage products.

All businesses, organizations, websites and governments use data to present information to users. This ranges from contact information of your company, products for sale, where you are and where you have been, and the transactions for your bank account. The right architectural practices for data management ensures successful scalability of your website, application or product managing data. There is no one way to architect a technology solution, however, there are many ways poor design can seriously impact the need for more hardware and resources, and most importantly, take significant time to re-architect causing development of new features to be affected.

Why is there a need for design practices?

Consider this question. How do you describe the need and role of a software architect to a non technical person? This is a generic description for the layperson.

You want to build a new home on a vacant block of land. In order to build a lasting home with all the expectations for today’s living, a lot of planning, design, and development occurs long before you can move in, paint the walls, add furniture, cook, entertain and reside.

Your home is built on a foundation that is generally a solid and rigid block of concrete that was carefully planned on a level surface. The plumbing and electrical wiring was carefully prepared before the concrete was set. If the foundation is not level, solid, and meeting the design plan then no matter how much work you put into your home, you will spend more time compensating for the deficiencies of the foundation. Do you spend more and more time and expense trying to compensate, or do you have to give up, tear down the failed attempt and build again?

Designing and developing a website or a software product uses the same approach. With software the amount of investment, the time, and expense is far less than building a home, however the lack of a plan and design can easily lead to a poor product that nobody wants to use.

The availability of open source software, free hosting and ample online resources enables anybody to take an idea and create a software solution with very little effort. If you are building a shack on the beach, you do not invest a lot, you accept the cracks in the walls, the leaky roof, the lack of plumbing. Your first software application will likely be exactly the same. Do you continue to add on, or is there a time you decide you want to start over for your family to live at the beach over the summer with all the expectations of a holiday resort?

Not all plans are the same. If you live in the Amazon, your home is not built with concrete, and has additional different needs for building higher off the ground to avoid tidal flows and to have room for food storage and livestock to have shelter. One size does not suit all. Just like there are shanty towns to million dollar penthouses in large cities, the diversity of websites and software solutions are analogous.

Understanding how to design and develop a successful and scalable software solution takes practice, trial and error, and a good amount of training. Working in a team that includes experienced resources that have completed projects ensures you can learn from the mistakes of others and cultivate a healthy environment for accelerated learning.

It is unfortunate that many startups and open source projects, even the most popular LAMP applications suffer from common design mistakes. These practices misuse MySQL and therefore can shine a poor light on the software and the misconception that MySQL does not scale. Many of today’s the largest websites, all the top telecommunication companies, online gaming sites and numerous other companies all rely heavily on your data stored in and managed with MySQL.

Today, many organizations have developers, IT managers and C level executives where the most senior resource has a handful of years of experience or less. They have little or no formal experience in design, data structures, software development, production deployment and testing methodologies. Combined with having never worked in a team environment with more skilled professionals, many software engineers today have a very limited world view of how large and successful products have been developed in the past. This lack of depth in skills reflects how the entire team learns and grows and without the right historical influences too many common beginner mistakes are repeated.