This website stores cookies. Click here to accept them.cookie information page

"databases" category

How do you save data to a database?

10th December 2011
Categories: basics, databases

Databases are a tool for data storage and retrieval.  They tend to be more useful than data files (i.e. simple text files) because they can be queried very quickly, due to the use of indexes. But it's worth noting that databases really are glorified data files, with some housekeeping processes and query tools to make them easier to use and maintain.

Let's stop for a moment to talk about indexes.

If you've ever read a reference book you'll know roughly how an index works: you can find an important word quickly in the index because the index is small. The index points to a page in the larger body of the book. But once you've found your word in the index, you can find it quickly in the book because you can quickly thumb to the right page. 

This is exactly how database indexes work, and why from billions of web pages, search engines can find what you're looking for before you've finished typing it. It is why any web developer worth his salt (and given a decent server) can make a website go fast... but I digress.

How will a database help me?

Databases not only do the indexing and lookups for you, a relational database will let you query by linking tables -- for example customers and orders. If you have a customer table with all the relevant information about a customer - email, contact details and so on, you may not want to replicate this inside every order. So you have two data files (or 'tables', as they're known in the business) and you can join them both in a query, like this:

SELECT customer.firstname, customer.lastname, order.orderid FROM
customer INNER JOIN order on customer.customerid=order.customerid 
WHERE customer.lastname='Smith' 

The lines above are called a 'query', and this is sent to the database system by whatever programming language you use, from one program to another on the same computer, or over the internet.  The database server then interprets the query (at lightning speed) and sends the results back to the software.

So instead of writing software specifically to query each table and to match up the results, you simply phrase the query for the database server to understand.  And instead of goint through each line in the data file checking against your search criteria, you leave the job to the database server and its lightning-fast indexes.

What are the choices?

There are too many choices of database server to mention here, but I will name a few:

MySQL is an open-source product and the world's most popular web db, supporting the vast majority of the world's data-driven websites.

MS SQL is Microsoft's database server, very robust and full-featured.

MS Access is a desktop database, not suitable for large or web-based applications.

Oracle is the traditional choice for big corporate finance systems.

Where do I go from here?

For a first time web developer I'd recommend getting started with MySQL - whether using PHP or ASP. Most web server installations come with MySQL installed, and for ASP you can download "connectors" from the website.  For windows desktop use, WAMP (Windows, Apache, MySQL, PHP) is a real life-saver and installs all of the main components for getting a dynamic website up and running.

© Alberon Ltd 2018

8 Standingford House
26 Cave Street

01865 596 144

Oxford Web is a trading name of Alberon Ltd, registered company no. 5765707 (England & Wales).