Intro to SQL Part B

In my last post, I introduced some of the basic ideas of data storage and how I came to realize that SQL would be a great solution to my content handling problems. Now I'd like to walk through some of the more advanced concepts and start going through some basic uses. There's plenty of great tutorials out there if you'd like to take SQL to the next level (I'd recommend checking out w3schools), but I hope these posts make a great foundation for a starting developer.

An Excel workbook is a good way to picture a SQL database. A database is a collection of tables (one table per tab), with each table showing a different portion of the total content. Each table should be unique in most cases to avoid duplicating your data. The individual tables in the database have some constraints. Rows must have the same number of columns and identical column types. It's also a good idea to keep the columns simple. If you're saving numbers, then the column should be set to save numbers.

The last paragraph may be hard to understand at first, so here's another example. This blog is being saved in a SQL database. One table is saving all of the posts, with each row in the table relating to a single post. Each row has a field for the individual pieces of a post: title, body, date created, category, etc. There is only one row for each post and each post has the same number of columns. While each row is fully unique in terms of content, there are similarities between the posts, such as the type of information being saved in the column and the usage of the data.

At first, this method of saving data may seem similar to the other procedures I described in the last post. After all, you can still save structured data using a well-formed XML document or even a strict layout in text files. The usefulness of SQL comes with the power of the query, or how you pull data out of the table. With a single line you can sort, sum, group, and more from one or many different tables. Compared to the foreach loops and manual script comparisons that other data storage techniques require, queries are incredibly easy to understand and implement.

There is a few things that complicate simple SQL, though. Going back to the blog example, you need to join tables together for one-to-many relationships like comments or tags. There can be many comments for a single blog post, so you'll need to create a key linking the comment table to the blog table. Trying to join many tables over different constraints can quickly bog up what was once a simple query and slow down your web site. An advanced concept called 'normalization', which involves taking all duplicate rows out of a table and creating a separate table to handle them, can also create complex join. Users who comment on a blog should have their names and information saved in a different table from their comment to allow easier updating and handling separate of their comments.

This is just an overview of SQL, but now that the potential usage and foundation has been set, we can start moving on to actual coding. The next post will introduce some basic SQL statements that can be used for reading and writing information to a database.