Tools: Compare Hosting

Database Tools and Web Hosting

There are many ways to interact with a database management system. You can use a command-line interface, issuing arcane SQL commands. You can embed equally arcane SQL code into an application. You can use a programmatic database abstraction layer such as the Active Record class in Ruby on Rails, or another ORM library. Or you could use a database management interface tool.

During the normal course of running a database-driven application, there usually isn’t much need for using a database management tool. If the application is well-designed, all database interaction should take place from within the application.

However, if you are building your own database application, you may need to use database interaction tools to set up, manage, and design your database as you go.

Types of Database Tools

Databases come in many different varieties. The biggest division though is between Relational (generally structured) and Key-Value (aka NoSQL – generally unstructured). There are of course myriad other classifications and theories behind database types, however, those two are the biggest conceptual frameworks for the majority of applications. 

Both of those paradigms have their own style of tools, however, both often will have a command line interface (CLI) as the most basic tool. Relational Databases will usually fit into a Relational Database Management System RDBMS, which is a way of abstracting the database storage, processing and memory model into a single database server application. This is the standard for all forms of SQL, which are often accompanied by a visual interface application such as MySQL Workbench or Microsoft SQL Server Management Studio.

NoSQL tools can also sometimes have visual tools, but they will generally function far differently from a RDBMS tool. There are also server cluster management tools, business intelligence tools, and dozens of other types of tools – which all perform some different capability.

It also important to note that certain programming languages and software libraries can function as database tools, where the line between database tool and data service tool can be difficult to discern.

What can a database tool do?

Each tool will have a different set of capabilities depending on which type of database it controls, however, some themes are common among many different tools.

For NoSQL databases, tools include a command shell or language API for managing key-value pairs, or some NoSQL technologies have visual interfaces for managing the MapReduce steps of distributed storage.

For RDBMS tools, the standard interface is a list of databases, each which contains tables, and each table can be viewed or manipulated like a spreadsheet, but the majority of control happens in a window where SQL scripts can be written and executed.

On top of running “Queries” – an RDBMS tool can also contain programmatic controls such as “Stored Procedures,” “Table Views” or reoccurring Tasks. This visual interface is either a desktop application or a web browser based control window. These interfaces make it easy to do the basics of SQL – which involves using the “Create Read Update Delete” (CRUD) approach to database management.

Often, the queries themselves will be passed through an unseen “Query Optimizer” which handles the search logic at a deeper level than the SQL script. Additionally the tools allow for tables to be setup in such a way that each table knows the contents of other tables through the use of Primary and Foreign keys.

Database tools and web application development

Databases can be used for a wide variety of purposes, ranging from accounting to game development. However, nearly every complex web application in existence uses some form of data service.

Without a data layer, all persistent information on a server would have to exist as a static file or take up room in permanent memory. Usually, the database is used to house information about users of a web application, and the content which they create. In the example case of a Content Management System (CMS), most use a database which usually is setup and managed automatically by the software.

Access to the database is usually available, but depends on how much control a web host provides. For most web hosts which use the popular “cPanel” tool, the database can either be accessed through phpMyAdmin or remote desktop tools can be setup by whitelisting an IP address and using the correct username and password.

Relational Database Design Basics

Whether you are issuing commands through your application, or setting up tables manually, you need to follow good data modeling practices.

• Visual model first — Whether you use a sophisticated database modeling tool or a pad of paper, it is much easier to get a sense of how the parts of a data model fit together if you draw a graphical representation of the model.
• Major items first — Data modeling begins with the most important (usually real-world) items that you need to keep track of. This might be people, project, products, pages, posts, or any number of other things.
• Relationships matter — The relationships between different types of entities is the most critical aspect of a database design. Be sure to think through the implications of different options for how to related items to each other.
  • One to one — In one to one relationships, there is often no reason to have completely separate entities. If every left-shoe has one-and-only-one right-shoe (and, let’s say they each a serial number), it would be more efficient to have a table of pairs, with a column for left-show number and right-shoe number, rather than two separate tables. Be careful, though, a database may need to track changes over time, so if pairs are exclusive, but not consistently permanent, you will likely need a different type of pairing.
  • One to many — If a single entity can be connected to several entities of another class, but each the members of that class can only be connected to one member of the first class, this is a one-to-many pairing. An example would be orders in an ecommerce system. Typically, a single customer can have many orders, but an order can only really belong to one customer. When building a one-to-many relationship, the relationship identifiers needs to be on the “many” side. That is, in the product example, a column on the Orders table identifies a row on the Customers table, not the other way around.
  • Many to many — Any member of one class may be related to any member of another class (or the same one, actually), and there may be an infinite number of these connections from either class to the other. This is implemented by a third table whose rows record paired items and usually a column indicating how they are paired. A non-obvious example of this is books in a library system. Any author may have written many books, but also any book may have multiple authors. Another design may have an Author column (or multiple Author columns) on the Books table, a more thoughtful design would have a Contributors table that pairs books and people, with a third column describing that relationship (author, editor, forward by, etc.). Usually, the description column would refer to another table of options.

Security Considerations

It is common for certain data to be encrypted before being passed into a database, which then requires a cypher when reading the data. The database itself is also be encrypted, and cannot be read without first logging in with the correct username, password and designated web port (for remote connection).

For data which is stored as encrypted strings, it is up to the developer to setup the encode/decode steps for making sense of that information, multiple security measures and encryption standards exist and are encouraged

. Some technologies also connect security components to the operating system itself, requiring network user certificates in order to access root level server privileges.

Common Database Mistakes

• No Foreign Key constraints — The whole concept behind relational databases is that items in one table can be related to items in another table. So it makes sense to ensure that their relationships are enforced (if, indeed, they are logically mandatory). However, many database designs leave out foreign key constraints altogether, making it possible to associate items in one table with non-existent rows in another table. This is especially problematic in MySQL, because one of the storage engines (MyISAM) does not support foreign key constraints.
• Flat multi-value attributes — Usually a result of assuming there would only be one of something, and it turns out there are several. First you have a Customer table. You have an Address column set (Address, City, State, Zip) on the table. Then you realize that you need billing AND shipping addresses, so you add a second set. Now you have Address and Shipping_Address. What happens when you realize you need multiple Shipping Addresses? The right way to have done this is to have a separate Addresses table, with a foreign key relationship to Customers. You have taken an attribute and turned it into an entity, and you can have as many related entities as you need.
• Age instead of date — If you need to know how old someone is, you might unthinkingly create a form to ask someone their age, and then assign the value to a database column for age. This is a terrible idea. You will never know when to update the age. There is no way to keep this data true. Use a birthday.

There is no single “right” way to use any database tools, but there are best practices which can greatly enhance the behavior and performance of a website.

Keeping database server logs is a good precedent for figuring out when problems occur. It is also possible to setup alerts based on certain conditions, which can either be disk space monitoring, or it can be a special type of report which is connected to the output of a stored procedure.

For example, someone running an e-retail website might want to receive a daily report which shows the number of customers from the previous day, along with the total amount of money earned, and the average spend per customer. 

These types of business intelligence reports can be constructed manually from scheduled SQL procedures, or they can be built from second layer database tools that specialize in reporting or analysis. At a broad level, these second layer tools follow the methodology of “Extract, Transform, Load” (ETL) and can be used to create those reports in the form of email, dashboards, or any other desired media.