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

What Is the Best Front-End/Back-End Combo for an Enterprise App?

frontend-backend-combo

Originally published Feb. 20, 2017, updated Aug. 15, 2018.

Choosing the best front-end and back-end combination is probably the most complicated part of building an enterprise app.

There are so many exciting languages, frameworks and databases to consider, and no single combo works in every situation. It’s easy for options paralysis to set in.

To navigate through the maze of choices, let’s take a closer look at the popular databases and front-end framework solutions in enterprise computing today.

Databases

Oracle

The longtime king of the hill in enterprise databases is Oracle.

For large government agencies, international corporations and high-value data centers, Oracle’s reliability is hard to beat.

IBM DB2

IBM’s DB2 is one of the highest quality, most cost-effective RDBMS platforms on the market.

It’s easy to install and supported on Unix, Linux, and Windows.

DB2 is ideal for companies running high-volume, high transaction workloads.

It’s most popular in industries like insurance.

Microsoft SQL Server

This versatile database platform offers enterprise companies complete data management and powerful business intelligence (BI) capability.

SAP Sybase ASE

This stable, cost-effective, high-performance, low-risk database has seen better days.

Under its previous name Sybase, it was part of the database power trio: Sybase, Oracle and IBM. It lost market share and was eventually sold to SAP.

The database still sees wide usage in industries like banking, but its star is falling.

Newer databases continue to edge it further into obscurity.

Teradata

For enterprise organizations with huge data warehouses, Teradata database fits the bill.

This self-described Very Large Database System (VLDB) was the first database focused on handling terabytes of data.

It features intelligent optimization which responds rapidly to requests.

Popular users include telecom companies and large retailers who handle enormous amounts of transactions every day.

EnterpriseDB

Based on the open-source PostgreSQL database, EnterpriseDB (EDB) adds performance and security features suited for enterprise-level workloads.

Front-End Frameworks

The field of top-quality front-end frameworks is constantly evolving.

JavaScript solutions like Angular and Ember have grown into an important element in many enterprise stacks.

Bootstrap

Since its release in 2011 Bootstrap has become the most widely-used open source framework in the world.

It’s a popular tool for HTML, CSS and JS development.

Bootstrap scales easily on websites and apps off a single code base across desktops and mobile devices.

Its documentation is incredibly thorough, though the variety of styles included makes the file size a little large.

Foundation

This enterprise level front-end platform used by eBay and Facebook is useful for creating highly responsive sites.

Foundation is a collection of frameworks for the front-end that creates attractive, fast sites, email messages and mobile apps.

The tradeoff is that it’s too complex for beginner; it takes a skilled developer to build with Foundation.

Angular.js

Angular has rapidly become one of the most popular JavaScript frameworks for enterprise computing.

It emphasizes simplicity, testability, and ease of construction, letting developers get more functionality from less code.

The framework is rigorously maintained by Google. Two versions are planned for 2018.

Version 6, which was released in early 2018, adds more tools for cross-compatibility going forward, and Version 7 is already in beta for a Fall 2018 release.

React.js

Like Angular, React has grown in popularity over the last couple of years, offering a series of new releases and development tools and enjoying increasing adoption for new projects.

Unlike Angular it’s not a fully-fleshed front-end. Instead, it’s a simplified way to create dynamic view layers for apps.

React got a serious boost in popularity from Facebook’s release of React Native for creating mobile apps.

Ember.js

Ember offers easy migration between versions and rock-solid stability.

It’s been trending downwards in popularity lately, but not because of quality.

Instead, the problem seems to be that Ember is highly structured and standardized, and most newer developers like JavaScript for flexibility and freedom of style.

Ember is useful for building highly scalable Single Page web apps.

Backbone.js

Backbone provides structure to enterprise front-ends, with features like key-value binding, collections with a robust API and views complete with declarative event handling.

Some developers regard Backbone as too light for modern apps, but it still has a loyal base of users.

Conclusion

These are just a few of the many dynamic tools available for building modern apps.

Choosing between them- that’s better done on a case-by-case basis.

There are some helpful guidelines when weighing the options.

For example, a solid MVC framework like Angular or Backbone is a reasonable choice.

Also, worth considering is that while the database and front-end are the core of enterprise installation, more tools are needed to optimize the system.

These could include components like API middleware, jQuery, underscore templates, and custom fonts.

The best way to create the perfect front-end and back-end combination is to outline the specific project at hands requirements and assemble the stack that fits those.

Looking for direction on your next enterprise app? Sit down with one of Concepta’s experienced developers for a free consultation on what’s best for your business.  

Request a Consultation

NoSQL vs SQL: Which is Right For Your Project? [INFOGRAPHIC]

nosql vs sql

As technology advances, the difference between NoSQL vs SQL databases can do are blurring. Developers flood tech forums with clever workarounds that let them stick to their favorite database, even when it doesn’t exactly fit.

This is a source of confusion for the c-suite seeking guidance on the best database for a specific project. No matter how hard the internet crowd argues that an experienced programmer can make any database work, there are very real structural differences between SQL and NoSQL.

Workarounds that disguise these differences result in unnecessary complexity, decreased performance, and higher development costs.

So how should a responsible leadership team choose? Start by brushing up on the benefits of both formats.

SQL: Orderly and Secure

SQL refers to relational database management systems (RDBMS) written purely in SQL. Data fields are organized into structured tables, and creators must clearly define the schema before adding data.

The rigid structure of a SQL database has some major advantages.

Advantages of SQL

Data Integrity

The format emphasizes data integrity by minimizing redundancy, normalizing data, maintaining referential integrity, and enforcing strict constraints on what data can be entered or altered.

Better Control

Using a SQL database provides better control of transactions compared to NoSQL databases.

Easier

A hyper-organized SQL database is also easier to query.

Handling large amounts of structured data

It’s suitable for projects with a large amount of structured data that needs to be regularly referenced and analyzed by non-engineers.

Anyone who knows how to structure simple SQL queries can pull information from the database (within the permissions imposed upon them by the administrator).

Community Support

SQL databases have been on the market for a long time.

There’s a large community of users, so support is easy to find.

Experts

With many skilled developers and database administrators available, upper management can afford to be choosy about who they contract with or hire to handle their SQL database.

Disadvantage of SQL

Cost

The biggest drawback to an SQL database is its price.

Hosting and licensing fees are generally higher than with NoSQL databases. Most companies offer bundle discounts, but costs rise when developers want to mix and match products to more precisely suit their needs.

The leading RDBMS, Oracle, changed its pricing structure in January so that hosting with Amazon Web Services or Azure is significantly more expensive.

However, open source options like PostgreSQL or MongoDB can keep the price within an acceptable range.

NoSQL: Fast and Flexible

The name “NoSQL” is a bit misleading. It means “not only SQL” rather than “no SQL”. In a NoSQL database, data is stored in documents that contain loosely related chunks of information.

Developers don’t need to create schema before adding information; data can be dumped into the database and organized once it’s there.

Advantages of NoSQL

A NoSQL database is useful when the data requirements of a project aren’t clear up front, especially if it’s difficult to estimate how much of the data will be structured versus unstructured.

Use of Unstructured Data

Document databases are perfect for large volumes of unstructured data.

Fast Development

Document databases are ideal for rapid development. Installation is quick and easy. Because there’s less setup involved before adding data, they allow for fast, iterative prototyping.

IDEs are Abundant

In their infancy there weren’t many NoSQL tools, but today developers have a wide choice of integrated development environments (IDEs) to examine their data and run queries.

Compatibility

NoSQL databases are compatible with most development frameworks and platforms which provides flexibility in building the stack.

Inexpensive

Plus, hosting is cheap on the production environment. Although a third party service is recommended afterwards, there are plenty of cost-effective cloud services to choose from.

Scalability

Scalability is a powerful benefit to NoSQL databases. They are designed to be distributed across servers, making them scalable on short notice with little impact on performance.

Accessibility

Data is accessible and very fast compared to most common RDBMS.

Disadvantages of NoSQL

Dip in Quality

Of course, flexibility and performance come with a reduction in accuracy.

Not ACID Compliant

The majority of NoSQL databases are not ACID compliant. Many rely instead on the principle of eventual consistency, trusting that although data may be altered on one server it will become consistent across servers during the next update.

Number of Experts

Finding NoSQL experts may be tricky. There is a growing community, but since it’s a newer concept than RDBMS there’s a smaller pool of trained developers and database administrators. Some supporters have argued that the point of NoSQL is to do away with the need for a DBA, though in practice that isn’t yet feasible.

Making the Choice

As a general guideline, NoSQL databases shine when dealing with real-time big data, content management, and IoT applications. SQL databases take the lead in situations where complex queries need to be done often, as in online transaction processing and banking.

That said, asking whether a RDBMS or a NoSQL database is better without considering the project is futile. It’s about as helpful as asking whether to use a fork or a spoon before the meal has been ordered. Each project has unique needs. While SQL databases tend to be the default choice for developers, there’s no objectively “better” overall database philosophy.

To make the final decision, assess the scope of the project at hand.

Are the data requirements clear up front?

Developers can’t create schemas without direction. When there isn’t enough certainty about what type or how much data will be incoming, a NoSQL database may be the better option.

 How much JSON data will you be handling as a percentage of the total?

Few projects will have entirely one type of data. If unstructured data is a relatively small portion of the whole and other considerations make an SQL database attractive, the SQL database will be able to handle it.

What’s more important, data integrity or performance?

When data integrity is the overriding priority, use SQL. If not, NoSQL generally has a noticeable edge in performance.

Will you need to cache and share data often?

This is a particular strength of NoSQL databases.

Is this a global or a local app? How fast will you need to scale?

Scaling a NoSQL database is as easy as adding more servers. An RDBMS scales up, not out, making for a slower process.

Are there relevant legal or trade restrictions?

Projects that handle a lot of protected data, such as HIPAA information, need to prioritize security and data integrity.

Concepta NoSQL vs SQL infographic

Once all the information has been gathered, one database style should stand out as the better option. If that doesn’t seem to be the case, take the problem to the developer. There’s a variety of both SQL and NoSQL databases; one may have a set of traits that solves the apparent dilemma. For instance, if a NoSQL database seems like a good fit but consistency is fairly important, the developer may recommend a database such as MongoDB which is strongly consistent (though not ACID compliant beyond the document level).

Still unsure? Concepta can help! Contact us for an assessment of the best technology for your project.

Request a Consultation

Choosing a Database: MongoDB Vs PostgreSQL [INFOGRAPHIC]

MongoDB Vs PostgreSQL

Ask a programmer whether you should use MongoDB vs PostgreSQL and you’re likely to open a can of worms. The core philosophies are very different, but loyal fans will argue that their favorite can be used for nearly every application. It is true that there’s a lot of functional overlap between the two databases. However, MongoDB and PostgreSQL have some specific differences that give them the edge in different areas.

MongoDB

MongoDB is a document database, meaning data is stored in cohesive documents that contain all the data about a particular topic. There doesn’t need to be a consistent relationship between the data. Because of this, MongoDB is an excellent choice for Big Data projects and social media applications dealing with large amounts of unstructured data (in short, anything that wouldn’t fit neatly into a table, like a blog post or a video). Use MongoDB for:

  • Storing large volumes of unstructured data
  • Fast, iterative development and prototyping
  • Scalability on short notice
  • Cheap to host, on production environment, third party service is recommended for hosting
  • Lightweight
  • High performance, data is accessible and very fast compared to most common RDBMS database
  • Easy installation
  • Easy to start development
  • Compatible with most of development frameworks and platforms
  • Many IDEs where you can see the data and run queries
  • Numerous hosting/cloud services
  • Large community
  • Good for solutions where you need to cache and share the data

PostgreSQL

PostgreSQL is an object-relational DBMS where data is stored in correlative tables. Although it’s a solid general use database, the focus on defining the relationships between data and enforcing standards makes it a popular choice for those who prioritize data integrity. Even MongoDB’s website credits PostgreSQL with being superior at “complex, multi-row transactions”. Use PostgreSQL for:

  • Applications that are highly sensitive to data loss (Banking, accounting, etc)
  • Enabling complex SQL queries
  • Prioritizing stability over scalability

MongoDB vs PostgreSQL Infographic

Check out this infographic for a comparison between MongoDB vs PostgreSQL:

MongoDB Vs PostgreSQL infographic

The Choice is Yours

It’s important to remember that no project exists in a vacuum; there are dozens of factors that may influence technology choice. A good software developer chooses the best tool for each situation, regardless of whether the project is a typical use case.

For example, you might expect that a tax and customs authority like the UK’s HM Revenue and Customs would use PostgreSQL. Tracking taxes seems to call for the rigid data integrity of an RDMS. There are over 65 million taxable subjects in the UK, however. HMRC badly needed the rapid development possible with MongoDB to get its tax planning and payment tools into the hands of the citizens as soon as possible. Using MongoDB, they were able to move from a glacial two releases per year to putting out 50 releases every week. The result was a 21% decrease in paper filings and wait times reduced by half.

Conclusion

A final word of caution: avoid choosing a DBMS based on outside factors like trends. It is neither easy nor cheap to switch later on if you don’t like how your database is performing. Data migration can cost 25-50% of original set-up costs (not including the potential risk of lost data) and take years to finish. Being open to recommendations from your software developer will save you the expense.

Unsure which database is right for your next app? Let the experts at Concepta help you decide!

Request a Consultation

MongoDB: A Document Database Used for Unstructured Data [REVIEW]

mongodb

What is MongoDB?

MongoDB is a document-oriented database that uses the C++ programming language. It differs from relational databases in that objects are stored in documents as opposed to tables. All data about the same object is found in the same document, which can be as complex as necessary through the use of nested data. MongoDB is innately schemaless, making it a popular choice for projects using large amounts of unstructured data.

History of MongoDB

In 2007 a group of DoubleClick executives founded 10gen. They tried to get a variety of projects off the ground but kept running into scalability problems. After several failures they switched strategies and began work on an easily scalable application stack.

In the early days they simply called their new database “p” for platform, but by 2008 it needed a name. Insiders like to joke that MongoDB got its name from a Blazing Saddles character. DoubleClick do-founder Dwight Merriman admitted that they chose the name from a deck generated by a naming consultant because it meant “big”. Regardless, the name gained popularity as the database did, and in 2013 10gen changed their name to MongoDB, Inc.

Features

  • BSON/JSON format
  • Load balancing
  • Horizontal scaling through Sharding
  • Native replication
  • Automatic failover
  • Supports dynamic queries
  • Data duplication
  • Indexing
  • High availability

Strengths

The schemaless structure of MongoDB allows users to store a lot of unstructured data (emails, videos, social media posts, etc) and perform complicated operations on it. Since as much as 85% of corporate data is unstructured, that covers a lot of ground.

Because the data is typically stored in one document, MongoDB queries are much faster. Users who create complex relational structures inside their documents may see slower returns, but this is mainly the fault of the user trying to apply the wrong tool for a job.

Another reason for MongoDB’s speed is that it uses sharding to distribute datasets and loads across numerous machines. Data is automatically balanced among the machines to avoid an asymmetric load. Sharding bypasses physical limitations of a single machine for faster, more complex operations. It’s also the secret behind MongoDB’s scalability: users can increase capacity by simply adding machines.

Weaknesses

MongoDB doesn’t support ACID transactions above the document level. That makes it less than ideal as a structure for applications “requiring multi-object commits with rollback”. MongoDB is not the best choice for accounting software or any other write-heavy application. (It does shine when an application is read-heavy, though.)

MongoDB has no native data validation; the user is entirely responsible for data integrity. Difficulty managing this has led to data loss scenarios for users without robust maintenance practices. In fact, maintenance in general is more demanding with MongoDB.

Real Life Applications

  • Metlife, the insurance provider, uses MongoDB to compile millions of customer policies.
  • MongoDB powers Scratchpad, Expedia’s innovative multi-platform travel planner.
  • Cisco uses MongoDB to power its collaborative workspace.

Future Outlook

MongoDB is currently ranked fifth among 330 database management systems. It’s capacity to handle unstructured data and lower infrastructure overhead have won it many loyal adherents. CEO Dev Ittycheria announced expansion into Europe, Asia, and Latin America this year, aiming to take market share from competitor Oracle.

However, Microsoft’s DocumentDB (now CosmosDB) has made a play for MongoDB’s customer base by offering support for their wire protocol. CosmosDB is too new to assess how many customers will be tempted by that; many clients prefer MongoDB as a more thoroughly-tested database.

If you have questions about MongoDB and how it might work for your application, Concepta can help. Schedule a free consultation to explore your options.

Request a Consultation

Cosmos DB: A Globally Distributed Database [REVIEW]

cosmos-db

What is Cosmos DB?

Cosmos DB (formally called Azure Cosmos DB) is an index-free database designed to manage data on a global scale. Its main goal is to make data available consistently wherever users are located. The database was created as a service more than a product, with features aimed at meeting the data needs of international businesses.

History of Cosmos DB?

In 2010, Microsoft engineer Dharma Shukla founded Cosmos DB to address the shortcomings in global data availability. Shukla tapped top talent for the project including Turing Award winner Leslie Lamport. As a stepping stone along the way Microsoft released DocumentDB, a proto-Cosmos product with less scalability and fewer consistency models (three to Cosmos’ five). Cosmos DB was launched at the Microsoft Build conference in May of 2017. All DocumentDB users have been transitioned to Cosmos.

 Features

  • Horizontal scaling
  • Schema-free
  • Graph database support
  • Automatic indexing
  • Multi-API support
  • 4 data storage models: Graph, key-value pairs, documents, and column-family
  • Web-based data exploration tool
  • Reported 99.9% uptime availability
  • Single-digit latency
  • Backed by comprehensive service-level agreement (SLAs)

Strengths

As new as it is, the scalability of Cosmos DB has it poised to become a leader in cloud-hosted databases. It’s geo-distributed across Azure’s data center regions, with custom distribution options available for each region. Software developers looking to build apps for international companies can make good use of Cosmos.

Cosmos supports graph databases, which are used by recommender engines and social networks to track their user’s behavior.

The database doesn’t lock users into one consistency model, either. Users can choose from strong, bounded staleness, session, consistent prefix, and eventual models depending on workload.

Total cost of ownership seems comparable to OSS options. In fact, Microsoft is claiming that Cosmos DB is at least five times more cost-effective. Time will tell whether that estimate holds true. For now users can enjoy an SLA promising 99.99% latency, consistency, availability, and throughput.

Weaknesses

Cosmos shares most of the general weaknesses of NoSQLs. Specifically, it’s new, untested, and lacks the body of knowledge backing RDBMS systems. Microsoft has been using it pre-launch, but there are sure to be unexpected issues. What are they

Also, while operation is surprisingly intuitive there aren’t many users familiar with Cosmos yet. Sourcing talent may present a challenge. (This isn’t a concern for those planning to outsource their software development and maintenance, of course.)

There’s no native local testing environment, meaning users have to be online. DocumentDB had the same flaw. There is an Azure emulator, though, and a host of Azure features that address other weaknesses of NoSQL databases.

Microsoft Azure only offers Cosmos DB as a PaaS. While this does allow them to keep the product updated and integrate new features, it also locks users into subscribing to the service. A system built on Cosmos can’t easily be moved.

Real-life Applications

Because Cosmos DB is so new, there isn’t a long record of users. Jet.com, an online marketplace competing with Amazon for market share, is the early adopter cited by Microsoft at the build conference. Jet records over 100 trillion queries per day. At the time of the press conference Jet was experiencing the single-digit latency expected by Cosmos.

Future Outlook

The digitization of the global marketplace is driving a need for databases that can keep meet demand. There are comparable products to Cosmos- Google Cloud Spanner, for one- but if it lives up to its promise Cosmos definitely deserves attention.

If you need developers who are experts with Cosmos DB, share with us your challenges and we’ll help come up with the right solution tailored to fit your needs.

Request a Consultation