How Databases Work

Almost all of the most useful sites on the web use databases to organise their content, and they often use them to allow users to register and leave comments too. Any time you do something that a website seems to 'remember' the next time, the chances are that a database is involved. Yet, despite how common databases are, they aren't very well understood. Every day, new webmasters become database administrators without even understanding the first thing about databases. When you use a database on the web today, you're not just using any database: you're using ones that rely on concepts built up over decades of database development and proven effective. Here are some of those concepts. Relational Databases The most common database model in use today is that of the relational database - others include hierarchical databases (where data is organised in 'trees', like an organisation's management structure), and flat file databases (where data is stored in 'records' in a text document). In a relational database, data is stored in tables. The columns are called fields and the rows are called records. So, for example, a table might have two fields: firstname and lastname. If you then added a record to this table, it could be 'Bob' and 'Smith'. Instead of just having that data, you have labelled it with what it is, and that lets you refer to it and search through it much more easily. Where the 'relational' part is really significant, though, is when it comes to the way tables in a database relate to the other tables. Each record of each table has an ID number (technically known as the 'primary key') - for example, the Bob Smith record might be ID number 123. This then lets you refer to his record in a new table. Let's say you were storing records of people's orders. You could have two columns: customer number and date. This lets you simply store 123 and the date in the table each time Bob Smith orders from you - the relational nature of the database will tell you later on that customer number 123 is Bob Smith. When it comes to things like, for example, storing posts made by multiple authors, this is powerful. SQL Databases SQL stands for 'Structured Query Language'. It's the most popular language for making queries to relational database systems. What's a query? It's basically a way of asking the database to find a record for you that matches criteria you specify. Let's go back to our example firstname and lastname table - let's say the table was called 'names'. To get Bob Smith's name in there to begin with, we would have used SQL that looked like this: INSERT INTO names VALUES ('Bob', 'Smith'); The ID number would be assigned automatically be the database. Then, later on, if we wanted to find out who customer 123 was, we could run this SQL: SELECT * FROM names WHERE id = '123'; This would get us customer 123's record from the database - Bob Smith's record. SQL might look complicated, and it can be, but that complexity is helped by the fact that there aren't very many SQL commands you're likely to ever need. Really, most websites can get by with just these statements: CREATE. Used to create new database tables. You have to tell the database which fields (columns) you want, and what kind of data (text, dates, etc.) each field is going to contain. SELECT. This command is used to search tables. You can use operators like = (equals), < (less than) and > (greater than) to find the record you're after. For example, if you wanted to find all your sales this week, you would work out the date a week ago and use SELECT * FROM sales WHERE date > - that is, "find all records in the sales table where the date is greater than...". INSERT. Lets you add new records to the table UPDATE. Once you've inserted data, update lets you modify parts of it. Useful if, for example, Bob Smith tells you he'd prefer to be known as Robert Smith. Update lets you change the data without having to delete and re-insert it, which means that records get to keep their existing ID nun DELETE. Removes existing rows from the table, using the same basic syntax as SELECT.