Oops! No Hosting Plans Match Your Search
You've selected a combination of features that none of the web hosts we profile offer. We suggest you remove your last filter or reset & start again.
Ask Our Experts
Need help with your hosting? Tell us exactly what you are looking for and we’ll do our very best to help. Please allow one working day for a response.
Please fill in all fields.
Thanks! Your request has been sent. We'll reply within 24 hours.
Relational Databases & SQL
All websites require some way to store content and data. The earliest websites didn’t make a distinction between content and presentation, and so content was simply stored in pages or documents, which were accessed by a web browser.
As the websites became more complex, and the technology to deal with this complexity advanced, content was separated from presentation. Content is now usually held in some form of a distinct data store. More often than not, this is a Relational Database.
Relational Database Basics
A relational database stores information in tables. Generally, there is a table for each class of “real” things which you are trying to keep track of. For example, in a library database, there would be a table for
Books and a table for
Authors (among many others).
Each row of a table represents one instance of that class — a single book, or a single author. Each column of the table is an attribute — title, year published.
The database is called “relational” because the tables can be related to each other. For example, authors are related to books in that each book has an author. So a column on the
Books table might be “Author,” and the contents of that column would be references to rows on the
Authors table (that is, reference to specific authors).
References are made by the use of Unique Keys and Foreign Keys. Each row on the
Authors table (and every other table as well) has a Unique ID, usually a number. In the
author column on the
Books table, the attribute for author isn’t stored as the author’s name, but rather as a numerical reference to the appropriate row on the
This way of referencing rows in other tables helps enforce the rule of “Single Source of Truth.” The
Authors table holds all information about the
Author, including how his or her name is spelled. If a change needs to be made, the change is made in only one place, and the referring rows don’t have to make any changes.
Data modeling in relational databases
The art and science of designing a database — deciding what tables are needed and how they will relate to each other — is called data modeling. And it’s more difficult than it may initially seem.
For example, let’s explore this library database further.
Should there really be a table for
Books? A library might have several different types of media available. Should there be a table for
Books and a separate one for
CDs? Probably not. A more sensible design would probably be to have a table called
Items, which includes as an attribute a column called
type. This would then be a Foreign-Key reference to a table that enumerates the different types of media (books, magazines, DVDs, etc.).
Authors? Should there be a table for Authors? What about cast members in movies, publishers of magazines, producers of albums? If someone has done all those different things for different projects, should they show up in four or five different tables?
That would violate Single Source of Truth.
Maybe we should just have a table called
People. But then, what about the
author column on the
Items) table? What if a book has more than one author? What if a book has an editor, but no single author (like a collection of poetry)?
The standard solution here is to have a table that defines a relationship between
People, perhaps called
Contributers. This would have three columns —
Role would then need to be a Foreign Key reference to a table which enumerates the different possible roles a person might have — author, editor, producer, actor, singer.
With data modeling, each piece of the puzzle — each “real-world” class of items you need to keep track of — creates a layer of potential complexity. Good data modeling is a process of finding the simplest solution which will allow for a maintainable and consistent dataset, in which any possible question about the data can have one and only one answer.
What kind of relationships exist between database tables? There are three kinds of relationships between tables. The most basic is the one-to-many relationship. In our phone call database, there is a one-to-many relationship between calls and caller tables. A single caller can show up any number of times in the calls table.
The one-to-one relationship is just what it sounds like. It is rarely used, because it is just a way of separating what would normally be a single table into two tables. But it is occasional used when there are a lot of data elements that are rarely accessed.
The many-to-many relationship is where there are many corresponding items in each table. And example of this would occur in our call log if there were conference calls with more than one caller for a single call. This is managed in database design by creating an intermediary table. This new table then has a one-to-many relationship with the two tables it is linking.
Most relational databases — and all relational databases which are popular for web applications — use SQL, the Structured Query Language. SQL provides a set of commands for manipulating tables (designing the database) and retrieving information (running queries).
Even though all SQL databases use the same query language for programming, each database management system implements the storage and management layers differently, which means that each has particular strengths and weaknesses in the area of performance of different types of queries.
Popular SQL Relational Database Management Systems
- MySQL — Probably the most popular RDBMS for web applications, owing to its use in WordPress, Drupal, Joomla, and several other PHP-based web applications.
- MariaDB — Drop-in replacement for MySQL with improved performance.
- MS Access — Desktop database application-building tool. Not generally considered appropriate for web use.
- MSSQL — Microsoft’s SQL Server, an RDBMS for use with other Microsoft applications and tools such as Sharepoint and .NET
- PostgreSQL — Community Open Source database, seen by many as a better alternative to MySQL.
- SQLite — Database-management library that can be included into applications. Stores databases as files. Useful for prototyping and embedded applications.
Hosting Frequently Asked Questions
What is a relational database?
A relational database is one that is divided into different tables, which have set relationships to each other. The critical factor is that no bit of information 'belongs' to any other bit of information. As a result, it is easy to search for arbitrary information.
What were there before relational databases?
Before relational databases, there were hierarchical databases. In these kinds of systems, data is organized in such a way that records belong to other records. This results in more complicated searching -- following from parent to child and on and on.
Who is Edgar F Codd and what did he do in 1969?
Edgar F Codd was a computer scientist who worked for IBM in the 1960s and 1970s. In 1969, he invented the idea of the relational database, which he shared with the world in the 1970 paper, "A Relational Model of Data for Large Shared Data Banks."
What is the Single Source of Truth?
The Single Source of Truth (SSoT) is the practice of only storing information once in a database. Consider a database of phone calls. It would display who called at what time. If there were five calls from Dennis Callsalot, the database would store the name "Dennis Callsalot" in just one place, and the call log would refer to that entry. Or to put it in database language, the call table would have a caller column. The caller column would include a number that references a people table.
What is the point of SSoT?
SSoT makes databases far more efficient. In our phone call database, we would waste a lot of space by entering "Dennis Callsalot" over and over. The smaller the database, the faster it is to search. It may not matter that much in a very small database, but imagine one where there are a million references to "Dennis Callsalot."
Another important factor is correction. Suppose the name "Dennis Callsalot" is wrong, and it is actually, "Dennis Calster." With a SSoT database, it is just a matter of changing the name in one place and then it is automatically and instantly changed in all applications that use the database.
What is SQL?
SQL is an acronym for "Structured Query Language." It is a specialized programming language that can be used to extract data from a relational database. The SQL queries can be very simple, just reading data from a single table. Or they can be very complex, reading data linked across various tables with numerous qualifiers.
How do you pronounce "SQL"?
Some people pronounce SQL as "S-Q-L" and others pronounce it like "sequel." Which is right? There is no definitive answer. Originally, SQL was called "Structured English Query Language," and was known as SEQUEL. So it was pronounced "sequel." But there was a trademark issue, so the name was changed to just "Structured Query Language," and known as SQL. Even after this, Oracle still referred to it as "sequel." But the developers of MySQL were very clear that the name of their database was pronounced "My-S-Q-L." Both pronunciations are perfectly acceptable.
Why is MySQL so widely used?
MySQL is an established and powerful database system. It was was the first major free database system to become widely used. It is the engine behind much of the software that makes up the internet, such as WordPress -- the most popular blogging software in the world. Its popularity also helps it in that pretty much all development tools must support it. But none of this should be taken to mean that MySQL's popularity rests on its market position. It is also easy to work with, secure, scalable, and quite fast.
Then why do many people claim PostgreSQL is so great?
PostgreSQL has a few advantages over MySQL. For one thing, MySQL does not implement the full SQL standard. For most applications, this doesn't matter. But for certain kinds of work, a fuller implementation is important. Also PostgreSQL is an object-relational database, which is an object-oriented database model -- making certain types of development easier.
What is SQLite?
SQLite is the most popular database management system in the world because it is used on all Android and iPhones. But it is not a client-server database like MySQL and PostgreSQL. It is a database library where it is used in individual programs.
What about Oracle Database? Microsoft SQL Server?
These are commercial database products with their own advantages and disadvantages. The primary advantage is support and the primary disadvantage is cost. The Oracle Database is object-relational whereas Microsoft SQL Server is a traditional relational database.
What about Microsoft Access?
Access isn't actually a database. It comes with the Jet Database Engine. Access is just the interface. It is possible to use it to access other databases.
Are there other commercial options besides offerings from Oracle and Microsoft?
There are many other commercial options. There are even commercial versions of MySQL.
Which database should I use?
This will depend upon your needs. If you are creating an Android app, you will almost certainly use SQLite. If you have a database project that is fairly straightforward but requires speed, MySQL may be the way to go. If you need a more feature rich database, PostgreSQL could be what you are looking for. If you need service and rock solid reliability, a commercial product may be the best choice. It all depends upon you and what you want to do.