SUMMARY: Reviewing basic SQL by datatypes and built-in functions. Groking joins, indexes, normalization, and syntax. Wrapped head around how a database is implemented and how it’s used.

After getting the rudiments of the database table creation / modification working last week in my code skeleton, I was hit full in the face by my spotty knowledge of SQL. I’d made several attempts over the past 10 years to get a feel for it, but I find it’s it to be an unwieldy language, and have always found other things to do with my time. However, there’s no more putting it off!

I decided to just do some reading on the subject of SQL. I looked at my wall of books, and found that the most recent MySQL book I had was from 2000, covering MySQL 3.23. I figured that most of the basics would be ok, so I printed out fast book outlining sheets and went to Starbucks to read in the last few rays of sunshine. Scanning the 700 pages of the book, looking for where the good information might be, took about 45 minutes.

Today, I looked back at my notes as a precursor to seeing what was there. Getting my head around SQL concepts isn’t as difficult as I thought it would be, probably because I’ve been exposed to bits and pieces of it while running my blog. At least the IDEA of selecting rows and columns is not foreign anymore, and I understand the premise of a table. What scared me off was all these stories about SQL Injection Exploits by hackers, and the difficulty of database normalizing and mastering JOINs. My impression is that people think these are difficult subjects, and they are often introduced as being difficult and complex. Taking a hint from a recent experience I’ve had figuring out 3D shader programming, it turns out that this isn’t really the case. The concepts are not difficult. The reason why applying the concepts is difficult is because the people who wrote the documentation find explaining the concepts difficult, and they are often unable to clearly separate implementation practices from the theory. This isn’t a prerequisite for being really knowledgeable and expert, but it IS necessary if you are trying to teach someone like me. Recognizing this, I can compensate by applying my own knowledge of the structure of algorithmic thinking to the subject matter as it is explained, and see “behind” the documentation and tease out what isn’t being explained. In other words, a teacher can tell me what to do, but I can usually make an accurate guess about WHY they do it that way; this makes me a challenging student, particularly for teachers who have been trained by rote and recipe.

So let me start making some pronouncements about how I should think about SQL, Normalization, and the terror of Joins:

• SQL is a language that applies an English-style grammar to a command language.
• SQL has a lot of the same computational features as the BASIC language, albeit implemented as a limited batch interpreter, one line at a time.
• A line of SQL is actually 5 separate independent command clauses, which are extended by built-in functions. The only required one is SELECT.
• When you want to get data from multiple tables based on how the data matches between them, that’s called a join. There are various ways that you might want to do this, and they have various names.
• Database normalization is the process of reducing redundancy in stored data. Storing an address twice, for example, is a redundancy.
• An “index” can be applied to a table to speed up read access. This is basically a way of telling the database engine to keep the table sorted in some way, which adds some overhead on write, but makes reads more efficient.

I outlined a second MySQL book this morning, and therefore have the rudiments of MySQL in my head. I will probably assemble my own reference at some point, or download one. The next step is to model the database structure for the purposes of making my simple ToDo list home page. Once I get that working, I’ll have the basic skeleton for a database-driven page in WordPress, and I can start adding functionality like sorting the priority.

The next step will be to take what I’ve learned and start designing more complex ways of portraying tasks and task timing.