Databases & Hosting: Everything You Need to Know

Narrow your search by type of Databases

• SQL
• NoSQL

Databases and Hosting

Almost every web application requires some kind of storage system for its data and content, and the most common type of storage is a database.

There are many different database options available, falling into two main categories — relational and non-relational. Each option has its own strengths and weaknesses when it comes to web hosting.

The concept of the database might have an origin prior to computing, but the first use of a data storage model was invented in the 1960s as a way to allow information to be stored, either for use in memory or for long-term storage outside of memory.

This has since evolved into a plethora of different technologies which all solve the same original problem but in more efficient ways. Today’s database technology fits mostly into two major paradigms, relational data (mostly structured) and key-value pairs (aka NoSQL, mostly unstructured), and of course a few other exotic categorizations exist.

Relational databases are mostly known by the model called Structured Query Language (SQL) and focus on recording transactional data. Unstructured data is generally focused on adding flexibility to user data for web applications, and is handled in such a way that information can be “mapped” across multiple computers more easily.

Databases and Web Development

In the early days of the internet, a website was usually a collection of HTML documents, often created individually. Eventually, people started using code like Server Side Includes so that repeated pieces of a page — the header, the footer, the menus — could be coded once and included in every page. This led to the solution that most websites use now: storing content in a database.

Today, the vast majority of websites are backed by databases. Some are very simple databases holding the content for a small blog. Others are incredibly complex databases, like the ones used by Amazon and Facebook.

Most website owners don’t get to make a decision about what kind of database to use. If you run WordPress, Drupal, or another popular content management or ecommerce system, the database choice is made by the developers. However, if you are building a custom application, you have a lot of options.

What database types work best for web development?

This can be a loaded question, as most database technologies are highly flexible in regards to how it can be used and what other software can use it. If a web application has an abstracted data layer, it can simply be told what type of database it is using, and it will automatically configure itself to use that data platform.

The biggest question for modern websites is whether to use relational or unstructured data storage. The first determining factor should always be the experience of the developer. For example, a MongoDB solution might be the best solution, but if a developer is more acquainted with MySQL, it will probably be faster to prototype features in MySQL. With that cleared up, NoSQL is structurally designed for storing data without the need to setup relational schemas. However, NoSQL is not optimized for transactional data, and relational databases work far more efficiently in situations where the data structure is always the same.

Once the structured/unstructured question is answered, the remaining decisions for picking a technology should be made based on what operating system, programming language, and root access permissions will be available on a selected web host.

Relational Databases (SQL)

Relational Database Management Systems (RDBMSes) are the most common type of databases. They are what most people think of when they think of databases.

Relational databases are made up of a series of interrelated tables. Each table contains information about a specific type of entity — like people, blog posts, products, transactions, or companies. Each row in a table represents one instance of that type of thing (one specific product, for example), and each column represents some specific attribute (e.g. price, name, color). Columns can relate to other tables, for example when a blog post has a column for author, which refers to a row on a table of authors.

Most relational databases use Structured Query Language (SQL) for commands, so relational databases are something referred to as SQL databases, as opposed to non-relational “NoSQL” databases (see below).

There are a lot of relational database systems, but a few of them account for the majority of database deployments, especially on the internet.

• MySQL — One of the most popular database management systems. MySQL powers WordPress, Drupal, and countless other systems. Benefits include excellent documentation, large user community, and plenty of free tools for modeling and managing databases.
• MariaDB — Fully compatible drop-in replacement for MySQL, with improved performance and additional features.
• MS Access — Microsoft’s desktop database system. It can be used in Windows to create ad-hoc database-powered applications, or connected to from other Windows platforms like SharePoint or ASP.NET. Access is not typically used as a web applications database, although it could be.
• MSSQL — Microsoft SQL Server, their version of a fully featured SQL database system. Works only in Windows.
• PostgreSQL — Powerful and open source RDBMS, the biggest competition to MySQL, and favored by developers who take themselves especially seriously. It is typically considered to be better at especially complex queries and operations, while MySQL is generally thought to be faster during simple queries.
• SQLite — File-based database utility built as a library that can be added into another application, rather than as an application unto itself. Often used for demos and rapid prototyping. SQLite is built into Ruby on Rails (though other databases are supported).

How a relational database is managed or constructed

A relational database, such as Microsoft SQL, MySQL, or PostgreSQL, can be administrated by a set of software tools known as Relational Database Management Software (RBDMS or RDMS).

Often these database tools are installed alongside the database itself, but third-party tools can also be installed sometimes. Once an RDMS is set up, creating the database “schema” becomes an important priority.

Some applications or web applications will manage database architecture for the user (such as a CMS) – however, for custom software, the database will need to be set up in a way that is organized and efficient. There are many different strategies to use here, where connecting one table to another is possible by using a “Primary Key” as a “relational” reference column and as a “Foreign Key” in another table.

In such a way, data structures called “schemas” can be set up. These schemas can be charted in such a way that a “data mart” can be set up, where some tables contain “facts” data and other tables contain “dimensions”. SQL statements can reference both fact and dimension tables to create many different data views for various uses from the same underlying information.

Looking for the right database host?
A2 Hosting scored #1 in our recent speed tests. They support SQL and NoSQL databases. Right now you can get up to 50% off of their dev-friendly hosting. Use this discount link to get the deal.

NoSQL Databases

NoSQL, or non-relational, databases do not follow the normal conventions of relational databases. Often, they have a more flexible data model than RDBMSes and do not enforce data normalization. This can speed up development and make the application’s data organization more accurate to a real-world domain that may not have such strict data definitions.

Depending on the type of data being collected, there can be significant read or write performance benefits. These benefits, however, come at the cost of the enforced consistency provided by traditional database systems.

• MongoDB — Probably the most popular NoSQL database. Mongo is document-oriented and stores data in a form of JSON, which makes it highly compatible with JavaScript-based frameworks like Node.js.
• CouchDB — Very similar to MongoDB in that it is document-oriented and JSON-based. It uses JavaScript as its query language (Mongo does not) and is highly available. Some of its advantages come at the cost of constant consistency: data propagates through the system in an “Eventual Consistency” model, which means that there may occasionally be times when obsolete data is returned by a query.

How non-relational databases are managed or constructed

Databases which use key-value pairs can be easier to install and often don’t require “structure” in order to use. Key-value data means that every data object has a data name and a data value, which might look like {name: “country”, value: “Canada”}, although many different syntaxes can exist.

Management of NoSQL databases consists of using command line tools, control via a programming language wrapper, or sometimes the use of visual tools in aiding with the MapReduce process.

The MapReduce concept is where all of the challenging work takes place, but results in enormous performance and scalability gains. The “Map” procedure handles information filters while the “Reduce” procedure does summary operations. Together, this makes for quick searches of big data volumes.

Database Tools

Having a database management system on your server doesn’t do you very much good if you can’t do anything with it. Some database systems offer built-in tools, but there are a few that need a direct admin panel separate from the application that is using them.

There is no official MySQL Web Interface, but phpMyAdmin is the “unofficial” interface. It allows you to create users, run queries, add or modify tables, and do any other database management task you may need to do.

A similar tool, phpPgAdmin, is available for managing PostgreSQL databases.

Undecided on a database host?
InterServer supports SQL and NoSQL. Their “price-lock guarantee” means that your hosting price will never go up. Right now our readers can get special pricing on their plans. Just use this discount link to get the savings.