WHICH DATABASE IS BEST FOR MY PURPOSES? A PRIMER


DATABASE PROGRAMS

  • SQL Server
  • MS Access
  • MySQL

DATA TOOLS

  • XML
  • Excel
  • Google Spreadsheet

DATA WORKING

  • Import/Export
  • Warehousing
  • Extracting


Now that we are talking about the most powerful databases out there, CLICK HERE to consider that the web's newer and simpler data solutions can serve the purpose at hand.

DESKTOP

A database serves one major purpose, and that is to hand data over to an application. Technically, Windows Notepad could be considered a (flat) database, and is often used to hold comma-separated values that can be read (parsed) by a program to put data into place.

A veteran data wizard knows there are times when Notepad is the tool of choice for importing and exporting data into and out of databases.

However, the ability to search quickly for specific data (query) and form more complicated relationships between data is what sets true relational databases apart from simple data lists like Notepad or even Excel.

Microsoft Access, released in 1994, was the first widely-used SQL-desktop database (SQL appropriately standing for Structured Query Language). It came with tools to create colorful forms that served as windows to the data.

Suddenly, personnel from the boardrooms, offices, and warehouses were setting up databases for every need. However, what was at first a boon to efficiency became a bane because data was scattered unpredictably over multiple computers. The reliability of data and its security was no longer optimal.

Moreover, storing data has a tendancy to increase by leaps and bounds. Witness the average hard drive, which has expanded from 20GB to 1TB in 10 years for the home user. The average storage has increased 50-fold in 10 years.

Microsoft has tried to move away from MS Access over the years, encouraging users to create a free SQL Server Express database on the desktop for the table structure. Then, by attaching it to a MS Access (forms), Visual Basic, or C++ desktop application, or to a web application like PHP, the forms to browse the data can be created.

Success has its pratfalls. None of these solutions are quite as easy to set up as just using a MS Access database out of the box. So, the huge popularity of the database outside of IT circles makes it a tough candidate to banish.

SERVER-BASED

Designed for the desktop, Access's limitation were best solved by server-based databases like SQL Server, mySQL, and Oracle, among others.

  • Multiple users could better read and write data without "trampling" each other. Access had tools to handle multi-users, but was never truly successful for high traffic situations.
  • These server-based databases had much more powerful (and quick!) search capabilities, as well as programming tools to set up better conditional search.
  • MS Access had a tendancy to become corrupt and lose data. It had compaction tools to remedy its intrinsic limitation, but multi-user systems had to be shut down entirely each time the maintenance wsa done. Server-based tools can be managed on the fly, and data corruption is rare.
  • Security is a moving target. It is more difficult to implement security updates on dozens of MS Access databases than to implement that update on one server database engine that runs all of your database programs and tables.
  • Once a poorly-written SQL statement damages data, the reversability of query changes in products like SQL Server becomes highly appreciated. .

WEBSITES

The role of a website determines whether it needs a database, as well as which kind of database(s) best suit its function. If a database is needed, frameworks abound to include database interaction. PHP, ColdFusion, ASP.Net, Ruby on Rails, etc., are some of the frameworks that are used to access a database in the background.

Many developers use MS Access to design the data structures while they do their desktop development, and then before the website goes live, they move up to a server-based database like MS SQL or mySQL, which is offered by most hosting services.

The database can hold data (like statistics, accounting values, etc., or it can hold the actual writeup you see on a website. In fact, many template websites like Wordpress use databases to store not just the text content you see on the website, but the colors, images, and even layout specifications.

It is not recommended to use MS Access for the live website's database within a website because of the limitations previously mentioned (as well as several other issues with interfacing with web servers.) Also, data is now housed on the Internet where the whole world could potentially access it. Access does not have the advanced security features to protect data seamlessly.


  • Riverside
  • San Bernardino
  • Rancho Cucamonga
  • Ontario
  • Moreno Valley
  • Chino
  • Redlands
  • Fontana