Databases — MySQL, Flat File, SQLite, Key/value, Cloud, or Just Use Access, Excel, KeyNote, or Arrays?
MySQL and Beyond
We prefer the ease of using MySQL, the ease of programming to it with PHP, and the fun of both. It is the basis of our Content Management System scripts, along with PHP. It is very versatile, dynamic, capable, fast, and stable and reliable. So what does MySQL's future look like?
Oracle, the world's largest business software company (they acquired Sun Microsystems, Inc. in January of 2010), announced the MySQL 5.5 release candidate in September of 2010. Oracle, the MySQL owner, says "it will continue to develop and promote MySQL as an autonomous entity, contributing improvements where it can without compromising the essential openness of the product and process. Oracle is wise to do so. MySQL provides access to database users that Oracle might not reach any other way. Its community represents a worldwide laboratory of development, support, and QA engineers who contribute the fruits of their labors freely in return for the free use of the software. MySQL enables Oracle to build upon its database user community from the grassroots, and to discover variant technology and usage models, at a fraction of the cost of conventional research and development. To interfere with that model would be to kill the goose that lays golden eggs, and Oracle apparently knows this."
Beyond MySQL, Oracle has dozens of products, not the least of which is Oracle Database, the world’s #1 business database, in several flavors, and you must buy a minimum of 5 of the $180 or $350 databases, but the minimum is 1 for the $950 db. And yes, Oracle licenses database, middleware, and management software for deployment in the cloud. What's that?
Cloud Computing—Not Just a Fad
Cloud computing that deals with large distributed systems is all the rage these days. What about MySQL—will it be left out of this?
ScaleDB is a pluggable storage engine for MySQL. It turns your MySQL application into an enterprise-class, highly-available, clustered database that scales dynamically in a public cloud, private cloud, or on premise.
And then there is Xeround's MySQL Cloud Database. With the rise of cloud computing and "big data", database scalability issues are becoming critical to the growth and success of many applications. Xeround's MySQL Cloud Database is an elastic, always-on database that lets you run your existing MySQL-based application in the cloud.
And let us not forget Amazon Relational Database Service (Amazon RDS), a hosted MySQL database cloud service (which charges by the hour).
So MySQL is a terrific cloud computer database as long as the scale is modest or one uses one of the above services, databases, or engines to increase the scale in an effective manner.
And the list of MySQL cloud databases and services goes on and on. So people are not going to be leaving MySQL in the dustbins of history anytime soon!
According to wikipedia, " . . . Cloud computing is Internet- ("cloud-") based development and use of computer technology ("computing"). In concept, it is a paradigm shift whereby details are abstracted from the users who no longer have need of, expertise in, or control over the technology infrastructure "in the cloud" that supports them. Cloud computing describes a new supplement, consumption and delivery model for IT services based on the Internet, and it typically involves the provision of dynamically scalable and often virtualized resources as a service over the Internet. The term cloud is used as a metaphor for the Internet, based on the cloud drawing used to depict the Internet in computer network diagrams as an abstraction of the underlying infrastructure it represents. Typical cloud computing providers deliver common business applications online which are accessed from a web browser, while the software and data are stored on servers."
So what kind of database fits the large distributed systems model? Or another way of looking at it is what happens when your mom-and-pop internet store becomes a huge success and your databases become inadaquate and you max out hundreds of servers? You need the cloud model, and the Key/value databases often found in this model. Key/value databases are item-oriented, meaning all relevant data relating to an item are stored within that item, which improves scalability by eliminating the need to join data from multiple tables, like with relational databases such as MySQL. So they work well in a cloud context.
For more on Key/value databases, check out the comparison charts here: Is the Relational Database Doomed? You'll see that relational databases' scale well, but usually only when that scaling happens on a single server. When you have huge data loads, requiring a large numbers of servers, you'll quickly see the limits on their viability as platforms for large distributed systems. And that is where the various cloud computing solutions like Key/value databases come in.
What About Flat (Text) File Databases?
Of all the flat-file databases, SQLite is the most widely deployed. SQLite is used on servers, but it is also used on desktop PCs, and in cellphones, and PDAs, and MP3-players, and set-top boxes. Think of SQLite not as a replacement for Oracle but as a replacement for fopen(), which, as you know, will open most any type of file for reading or writing as long as you have the proper file permissions (CHMOD). SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform. SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). SQLite is a good fit for use in cellphones, PDAs, set-top boxes, and/or appliances. It also works well as an embedded database in downloadable consumer applications. On the other hand, you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem, or in high-volume websites, or with very large data sets, or where there will be a large number of users using the same db at once online, or where a relational db like MySQL would serve better.
A really simple flat file database with column sorting can be found here: Flat File Database Demo. It would be a great one to modify and experiment with, since the code is shown and discussed. There are plenty of other flat file databases out there as well.
What About Microsoft's Access Database?
We've used the Access database and found it very useful to keep track of things like wildlife statistics and book bibliographies, without the need for a special db server like with Oracle, MySQL, etc. Access is a computerized database that is a container of objects. One database can contain more than one table. For example, an inventory tracking system that uses three tables is not three databases, but one database that contains three tables. Unless it has been specifically designed to use data or code from another source, an Access database stores its tables in a single file, along with other objects, such as forms, reports, macros, and modules.
Integrate your Access reports using multiple data connections and information linked or imported from other sources. With improved Conditional Formatting and calculation tools, you can create rich and dynamic reports with visual impact. Access 2010 reports now support data bars, enabling you and your audience to more easily track trends and gain insight. Access your application, data, or forms from virtually anywhere. Drag and drop functionality. Publish, back up, and manage your database with just a few clicks. Access 2010 empowers you to make the most of your information—even if you’re not a database expert. And, through newly added Web databases, Access amplifies the power of your data, making it easier to track, report, and share with others.
What About Microsoft's Excel Spreadsheet?
We've used the Excel spreadsheet and found it very useful as a database to keep track of things like software inventories, Google keyword search frequencies, video clips info, and vitamin inventory and tracking. By entering formulas into our vitamin spreadsheet, we've taught it to use its spreadsheet capabilities to track vitamin inventories and usage and display "Wait" in rows where we needn't order more yet, and "Buy" in rows where we have under 10 days supply left and we need to order more of that vitamin. Examples of how we use Excel as a database: address book, product history, product inventory management, vitamin tracking. For spreadsheet use, we have Excel be a major tool for financial management, budgetary calculations, economic prospectus, mileage calculations, cost of goods tracking, investment tracking, etc. The more you need to keep track of things and calculate things with formulas, the more you need Excel.
Microsoft Excel 2010 makes it possible to analyze, manage, and share information in more ways than ever before, helping you make better, smarter decisions. New analysis and visualization tools help you track and highlight important data trends. Easily access your important data on the go from almost any Web browser or smartphone. You can even upload your files to the Web and work simultaneously with others online.
What About KeyNote?
KeyNote is a free tabbed notebook with a Rich-Text editor, multilevel tree notes and strong encryption. It's ideal for free-form or structured information. The application stores many notes within one file. Each note can contain a hierarchical tree of topics. KeyNote features powerful editing, formatting, and search functions, and macros, templates, and plug-ins. It has an extremely configurable interface and behavior. You can import and export: text, RTF, HTML, and TreePad. It has clickable hyperlinks and the ability to drag and drop, and is freeware and open source. Is it a database? Yes. It's a great local db especially good for notes and personal info. Look elsewhere is you want a db that relates well to websites, the Internet, or cyberspace in general. Consider clickable hyperlinks its only relation to things online. We use it and like it a lot.
What About Arrays?
We've stored dozens of insult words in a PHP array for a PHP script for using Ajax and PHP to create an insult auto-completer, so that as soon as you start typing, the script takes your characters and finds all words that start with that letter(s) that are in the PHP array. It uses Ajax to refresh only the insult suggestion area of the page with in-real-time insult suggestions. Example: The other day we were trying to come up with the word or phrase that best describes Obama. The mere typing of the letter A got us a whole slew of suggestions but we quickly settled on the obviously most appropriate one: "ass clown."
You may put PHP arrays in the body section or simply use includes in this way:
<?php include("important-wonderful-arrays.php"); ?> or
<?php echo $my_array; ?>. See the bottom of our JSON page for more examples. Note: the values displayed like this cannot be changed without changing the PHP array values in the code or using an Ajax script to run a PHP script.