10 Database Design Best Practices

database-design-best-practices

Originally published Nov. 18, 2016, updated Sept. 6, 2018.

Good database design is essential to building scalable, high-performance applications. 

A database is nothing more than a mass of information stored in a framework that makes it easier to search.

Everything else is just details. If a database works well, bits of related information are filed automatically and details can be pulled out needed.

It should be simple to draw new meaning from data by compiling it into reports and visualizations, then storing away those facts for later use.

Within that simple definition, there is infinite variation. Small decisions in the beginning have a huge cumulative impact.

Follow these guidelines to avoid common missteps that can affect software down the road.

Database Design Best Practices

Before defining a single field on a project, look over these 10 database design best practices that can dramatically simplify the development process.

1. Consider Every Viewpoint During Planning

Don’t start building a database without input from the project sponsor and other stakeholders.

Get consensus on precise expectations, and consider how hard it will be to train users on the search functions.

2. Choose A Database Type

This is usually as easy as deciding between SQL and NoSQL (though there are more specific types that may be appropriate for some projects).

SQL databases are the standard for structured data, when data integrity is absolutely important.

Emerging technology like machine learning or the Internet of Things (IoT) find the speed, scalability, and fluid requirements of NoSQL databases a better fit.

Web analytics, social networks and some other types of databases also work much better within the NoSQL framework.

Make the decision as early as possible.

3. Normalize Your Data

In reality, most companies today function in a hybrid world of SQL and NoSQL databases that work together in complex arrangements.

With such a complicated structure, it’s critical to normalize data to achieve minimum redundancy.

Eliminate multi-valued attributes and repeated attributes, then start in on the subkeys.

4. Make Structures Transparent

The database belongs to its future users, not its creator, so design with them in mind.

Stay away from shortcuts, abbreviations, or plurals. Use consistent naming conventions.

Don’t reinvent the wheel or make things difficult for those who may need to modify the database at some point, which will certainly happen.

5. Define Constraints to Maintain Data Integrity

Look into the full range of options to enforce business rules, such as foreign key, check, not null, and the like.

The application will prevent some bad data from getting in, but not all of it.

6. Document Everything

No matter how annoying it may seem, documentation is as essential as primary keys.

Take care to document the design, entity-relationship schemas, and triggers for future users.

7. Plan for Increasing Backup Time in the Build

Before delving too deeply into design, think about what happens during a natural or man-made disaster.

Plan for failover clustering, auto backups, replication and any other procedures necessary to ensure that the database structure remains intact.

As the saying goes, “Prepare and prevent, don’t repair and repent.”

8. Keep Privacy Primary

The GDPR signals an era of increasing privacy concerns.

Encrypt passwords, and don’t assign an administrator without privacy training and well-documented qualifications.

This can be a tricky rule to follow due to office politics, but as a good security practice the database should be as closed as possible.

Vulnerabilities impact data integrity, which impacts everything else in the enterprise.

9. Optimize for Speed

Create indexes for queries that will be used regularly. Use a database analyzer to determine if an index or a clustered index is necessary.

Consider incorporating tools like Elastisearch to speed up searches.

10. Keep the Database on Its Own Server

Put the database on a different server than the web to lower CPU usage.

In addition to freeing up compute resources, it also helps to keep the database out of the reach of unauthorized users.

Final Thoughts

Remember, database frameworks are not set in stone.

The current database workflows can be refined and directed, or even broken up, when it make sense.

Just as business directives change over time, the database will also need fine tuning to stay in line with the company’s current goals.

Too often, administrators get bogged down in thinking about how the database functions at the moment without considering what it is capable of doing in the future.

Instead of thinking, “It doesn’t work like that,” start from the viewpoint of “What is the end goal, and how can we get there?”

Having trouble deciding between database styles? Sit down with one of Concepta’s developers to discuss your unique requirements and how to build the best foundation for your next software project!

Request a Consultation

The Dos and Don’ts of Scalable Architecture

Design-Scalable-Architecture

Scalability is having its moment in the sun. After being dismissed as something to think about down the road, leaders have begun to realize how important it is to plan for scalability from the start. This strategy puts companies in a solid position to take advantage of unexpected growth without taking a hit to service quality.

As scalability grows in importance, some common themes are beginning to emerge. Companies are realizing their legacy architecture is too rigid to handle a sudden increase in workload, and adjusting for growth is more expensive than they’d planned.

Read on to find out where they’re going wrong and how to learn from their mistakes.

Principles of Scalability

Simply put, scalability is the ability of a system to handle sudden changes in workload without negatively impacting performance. It’s usually broken down into three areas.

  • Availability: The system should be available for use as much as possible (ideally, always). Uptime percentage has the most immediate effect on customer experience. After all, it doesn’t matter how useful an application is if no one can access it.
  • Performance: The system must maintain a high level of performance even under heavy loads. Speed is critical to providing a good user experience, and customer experience is fast becoming the most important factor in preventing churn.
  • Reliability: The system must accurately store, retrieve, and edit data under stress. It should return the most current data on request without defaulting to old or inaccurate data or failing to record new data. Unlike availability and performance, reliability builds good customer experiences in the long run rather than just in the moment.

How to Design Scalable Architecture

Rather than focusing on specific brands or tools, keep a set of design principles in mind.

Don’t use vertical scaling

Vertical scaling is scaling by adding more powerful resources (ie, more RAM). It’s secure and fast under light loads, but it does not scale well at all. More powerful equipment is increasingly more expensive, and there’s only so high it can scale. Plus, vertical scaling tends to lock companies into technology without an easy path to modernization. It’s unavoidable sometimes- especially when dealing with many atomic transactions or high-grade security concerns- but whenever possible don’t scale up.

Do favor horizontal scaling

Horizontal scaling involves adding more nodes to a distributed network (ie, adding another server) rather than more powerful nodes. It’s the fastest, most cost-effective way to scale since increasing its capacity is as simple as increasing the size of the network.

Don’t default to physical servers

Physical servers are only really valuable for multinational companies with high security requirements and a lot of usable capital. For the vast majority of companies, they’re a waste of time and money. Servers are expensive to build, maintain, and secure. New projects have to be put on hold while waiting for the storage to be finished. Plus, there’s always the risk of hardware becoming outdated.

Do take advantage of cloud storage

Cloud storage puts data in logical pools spread out over a number of servers. Vendors then sell access to that storage through subscriptions. This model takes the burden of security and hardware maintenance off companies and allows them to purchase only what is needed at launch while remaining prepared for rapid scaling. Because the cost is spread out like a utility instead of an upfront investment, the project can reach ROI faster and begin to pay for itself much sooner.

Don’t create unnecessary bottlenecks

Bottlenecks form when multiple processes require the same resources to proceed. Plan the application architecture to maximize the availability of resources that will be in high demand. What does that look like?

  • Caching stores data closer to end users. It’s an excellent way to facilitate the return of data that’s needed often but changes rarely.
  • Non-blocking IO calls serve more requests with limited resources by letting processes continue before a slower one has finished.
  • Load balancing software intelligently spreads the workload across the network to reduce pressure on any one single node.
  • Redundancy safeguards against lost data as well as providing more access to high-demand resources.

Do consider a microservice architecture

Microservice architecture breaks large software into smaller pieces that can operate independently of each other. When one node is in high demand others can work without adding to its load. Fault isolation is a major advantage of using microservices. If something does go wrong, the majority of the system can usually remain up while that part is repaired.

A Final Thought

Choosing components which lend themselves to a scalable architecture saves both time and money. The slightly higher cost of emphasizing scalability during development is offset by greater agility and lower operational costs.

Concepta specializes in the kind of scalable architecture businesses need to meet the demands of digital transformation. Set up a complimentary appointment to discuss how to position your next application for success!

Request a Consultation

Building Your Web Application For Growth

Web-Application-Growth

When talking about building applications for growth, the conversation often devolves into a technical debate over whether to scale up or out or which specific technologies will suit an application. These debates forget one of the most important rules of enterprise technology: business goals should drive the tech, not the other way around. Leaders should start by clarifying what is needed from the app as it grows before nailing down the tools.  

There are three key concepts which all scaling technology should support in order for an app to be considered “built for growth”.

User-based Performance

Application performance can be measured many ways, but for public-facing apps the focus should usually be on user-centric metrics.

“User-centric” refers to traits which directly affect the user’s experience. For example:

  • Load times
  • Reaction times to button clicks
  • Fast and reliable return of updated information

Applications are highly sensitive to user perception. More than half of users will abandon a web app entirely if it seems too slow.

It damages their overall perception of the sponsoring business’ modernity, as well.

On the flip side, better performance leads to higher revenue and better ROI on advertising campaigns.

Apps that load in 5 seconds or less see 25% more ad visibility and 70% longer session duration on average. They’re also enjoy higher user retention rates than slower applications.

When deciding between two technologies, choose the one that reduces the user-perceived delay the most.

Determine how much of the delay is caused by each step of the data retrieval and presentation process, then prioritize tools that target the problem areas.

Scaling Cost

Web apps shouldn’t have a bigger cost to grow than to build in the first place, but that’s what can happen when growth isn’t considered from the start.

Companies sometimes make design choices that are less investment during development but require complex, expensive scaling techniques.

An app can’t be called fully scalable unless it has a fixed marginal cost to grow. Adding users or capacity should be predictably priced and simple to implement.

“Predictable” is a big part of being scalable. There’s no way to tell exactly how quickly an application will grow, but it is possible to create a scaling plan that shows leaders how much they can expect that growth to cost at each stage.

Surprises when scaling generally mean the original plan wasn’t thorough enough. Be sure to take into account vendor stability and projected rate increases.

Also, don’t leave out any higher costs for maintenance at scale.

Security At Scale

Application security is one of the top concerns for CIOs in 2018. 45% of companies plan to spend more this year than they did in 2017.

Despite the interest, 90% of web applications have at least one avoidable vulnerability. Common weak areas include encryption, authentication, and access control.

Hard-coded passwords are another serious risk. A full tenth of applications still use them for administrator-level privileges despite general discouragement by the security industry.

The risks from these vulnerabilities increase exponentially as an app grows into an appealing target for malicious actors. When planning for scalability, focus on growing securely as well as quickly.

Don’t take risks with customer data that could lead to breaches, legal issues, and loss of reputation.

On a hopeful note, the problem does seem to be shrinking. In 2016 the average number of vulnerabilities per application was four, and that fell to three per app in 2017.

That average may fall even more if the emphasis on security continues to drive safer design practices.

The True Measure of Scalability

Scalability isn’t only about whether applications do or don’t crash under unpredictable workloads. Availability is part of the puzzle, but not all.

The true measure of scalability is whether a system is flexible enough to efficiently meet a company’s needs within an acceptable range of quality regardless of workload.

Make that the focus of technology decisions and an highly scalable app will always be the result.

Concepta has more than ten years experience designing web applications that maintain high performance and security at scale. To learn more about the technologies we use and how they can benefit your business, set up a free consultation with one of our skilled developers.

Request a Consultation

Key Considerations For Building Scalable Web Applications

scalable-web-applications

The growing emphasis on digital transformation has led companies to focus on one area of development which was once considered an afterthought: scalability. Companies stand to reap healthy rewards from their enterprise software and analytics initiatives; it makes good business sense to prepare for that success by building for scalability.

Vertical Vs. Horizontal Scaling

There are two different schools of thought about scaling:  vertically – called “scaling up”- and horizontally – called “scaling out”.

Vertical scaling begins with one central system and adds more resources (like more RAM) to raise its capacity.

In horizontal scaling, the emphasis is on distributed architecture. Capacity can be increased by adding more nodes to the system.

For most purposes, horizontal scaling will be easier, cheaper, and more performant.

Vertical scaling has lower administration and operation costs when the server is on site, but with cloud databases becoming the norm that isn’t a consideration.

Requirements of Scalable Applications

What does scalability look like? There are three areas where an app needs to excel to be considered scalable.

  • Performance: First and foremost, the system must operate well under stress with low latency. User experience is becoming a key brand differentiator. If UX suffers as more demands are placed on an app, there’s a serious risk of falling retention rates.
  • Availability and Reliability: These are closely related and equally necessary. Scalable apps rarely if ever go down under stress. They need to reliably produce data upon request and not lose stored information.
  • Cost: Highly scalable apps don’t have to be unreasonably expensive to build, maintain, or scale. Planning for scalability during development allows the app to expand as demand increases without causing undue expenses.

Components Of Highly Scalable Architecture

When scalability is a priority, developers have several options for building it into an application’s architecture. Here are a few of the most helpful:

  • Cloud StorageUsing cloud storage allows companies to buy only what they need when the app is created. Adding more storage is as simple as upgrading a subscription. New storage is ready to use right away, no maintenance or construction fees required. Cloud storage lowers development costs since the upfront investment is relatively small. There are plenty of dependable options for SQL or NoSQL cloud databases available, too, so developers can choose one that meets the specific needs of a project.
  • Caches: Data that’s been called up once is likely to be needed again; caching puts it where it can be accessed more quickly. A distributed cache structure works well for distributed architecture. Data is cached with nodes that check with each other before checking database. The lets the system store data closer to the user for faster access.
  • Load-balancing Software: Horizontal scaling wouldn’t be as popular as it is without load-balancing software. It tracks stress on nodes and services, then spreads the workload across nodes to prevent bottlenecks and avoid applying too much stress to a single component. Apps that use load-balancing software handle sudden spikes or dips in demand better than their counterparts. The software makes adding nodes easy, too: developers just have to change the rules for handling a new node and routing traffic.
  • Proxies: Think of proxies like middlemen. They’re essentially intermediary software that can be used to coordinate multiple server requests. Proxies collect simultaneous duplicate responses to a server, send a single request for the information, then share the data out among all the requests for the same resources. Using a proxy raises latency rises slightly, but stress on the server drops significantly.
  • Microservices: Microservices are large services split into a number of loosely connected smaller services. Components can then be scaled independently of each other depending on actual usage. Microservice architectures handle asymmetrical demand well since only the service that’s needed is used while others are free. These systems are faster to develop and easier to upgrade.

Scalable From The Start

The decision about how to approach scaling should be made up front. Though planning for scalability is surprisingly simple, shifting directions down the road is complicated and expensive. Make this a talking point when discussing requirements during Discovery.

At Concepta, we’ve created beautifully scalable apps for clients as large as Disney and Warner Music Group. Schedule a free, no-strings consultation to discuss how we can build your next app with an eye to future growth.

Request a Consultation