So you want to build a data driven web site but don’t know what database to use? Maybe it’s an IOS or Android application and not a website at all. Either way, if you collect data, you’re going to want to determine the best way to store it for later. In this article, I’ll talk about what a database is, how to choose the right one, and what comes next.
What Is A Database?
By definition, a database (also commonly referred to as “DB”) is a collection of organized data. For example, if you have written down a list of all the CDs you have in your music collection, a database to represent that might include a table for artists and a table for albums and you could even link them together. This organization of data gives us the ability to work with data in just about every way imaginable, which is so much better than just a list. Examples of this can be seen right here on JoeTech.com. Articles on the main page of the site are shown newest first by sorting the article data, the search option at the top brings back specific articles by using MySQL searching and indexing tools, and your comment on this article gets stored in a comments table so that it can be retrieved later. There’s a lot to know about how databases work, but let’s start with some basics.
Types Of Databases
New databases (like MapD) pop up all the time, and it would be impossible to cover them all. In an effort to keep things simple, I’ll stick to some of the more popular DBs in use.
Some common object oriented databases that have been around for quite a while include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle. Each database system has its own features and flaws. Oracle is well known for its enterprise stability and support while SQLite is fast, free, and well adapted to smaller data sets. MySQL and Microsoft SQL are extremely popular for use with PHP or ASP/.NET applications, respectively.
In what sounds like competition for the traditional database systems, NoSQL databases have emerged in recent years and have grown quickly in popularity. Known for their ability to easily scale horizontally and store a lot of simple data (like Tweets), NoSQL databases often compromise by lacking a lot of functionality like normalizing across a large number of tables. Some popular NoSQL DBs include MongoDB, memcached, Redis, and CouchDB and can often be easier to get started with due to their simplicity.
A third category of databases that is coming into the light but not a highly used is NewSQL. This category includes new DB technologies that allow for highly scalable databases without the loss of critical functionality for broad data sets. These are perhaps not as critical when you’re starting out, so I won’t go into further detail.
How To Choose The Right Database
Choosing the right database can be paramount to the success of your project and the larger the project, the more important your choice will be. With so many choices, how do you pick the right one?
Before anything else, you need to know your goal. What kind of data – and how much of it – do you expect to store? One thing I look at is how many tables I plan on storing. If it’s just a few, I might default to a NoSQL solution, but if it’s a bunch, I’ll look to a traditional object-oriented DB.
After narrowing down the category of database to use, you’ll need to pick the best one for the job, your budget, etc. If you plan to house your site on a Windows server and program it in .NET, MS SQL is probably a good bet. If you want free and need to have everything on a Linux server with your code in PHP, MySQL is often the way to go. When the client (or your company) demands support contracts and has a budget to deplete, Oracle could be a contender. Look at the specific features and pitfalls of each database before making a decision.
What Comes Next?
Knowing the right database to use is only the beginning. Depending on your hosting environment, you may need to take steps to get the database software installed and then you should:
- Think through all the data you plan to store – every little detail
- Design a database schema – more on schemas in Part 2
- Normalize, index, and otherwise optimize your database – Part 3?
- Connect to your database from your site’s code
- Secure against hacking and injections
As you can see, there’s a lot to consider when it comes to storing data for your website or application, but if you take it one step at a time, it’s not so bad. Stay tuned for additional pieces to the database puzzle.