Databases

Updated on September 4, 2007

All files contain data, but a database imposes a structure on the data so it can be easily read and modified by an application.

Flat files

The simplest type of database is a flat file database. Flat files don't support features such as concurrent writes or locking, but are very useful for transporting data between servers or applications. CSV is a popular flat file format that is supported by a variety of applications.

Relational

Relational databases consist of related tables. Tables consist of rows and columns of data and each column adheres to a type of data (word, number, date, etc). Multiple tables can be linked or joined via a common column. For example, you could a have a table of customer data and another table of sales data. Each table could have a column called "customer_id". Each customer would have a unique id and each sale would have a customer id associated with it. A relational database manager could then let you look up all sales associated with each customer and all customer data for each sale.

Popular relational databases include Microsoft Access and FileMaker.

SQL

SQL databases are relational databases which can interpret SQL commands. Instead of directly accessing the data stored in tables, a client sends a command to a SQL server which then accesses the tables, performs the requested operation and sends the results back to the client. An example of a SQL command would be "SELECT name FROM customers WHERE customer_id=1001" which would retrieve the name of the customer with the id of 1001.

Passing all requests through a central server offers many advantages. A SQL server can handle requests from multiple clients and prevent the databases from getting corrupted (for example, if two clients try to add data to a database at the same time). Also, a SQL server can easily interact with clients over network.

Over the years, SQL database servers have become increasingly sophisticated and support features such as transactions and row-level locking which make SQL databases a better solution for busy databases and databases which handle sensitive data such as financial transactions.

XML

XML is a standard for encapsulating data by the World Wide Web Consortium. Unlike tabular databases, XML data is stored in a hierarchal tree structure. Instead of storing customers and sales in separate tables, an XML file might look like the following:

  <Customer>
    <Name>Acme</Name>
    <Phone>876-543-9876</Phone>
    <Item quantity="1">Widget</Item>
    <Item quantity="2">Gizmo</Item>
  </Customer>

XML data can be viewed in a web browser. A transformational style sheet can present the data so it looks like a regular webpage.

The W3C recommends the XQuery standard as a method for clients to access XML databases in a manner analogous to SQL, but the major SQL database vendors have also added proprietary methods for doing so.

Please give us your feedback.