I’m a little sad that hobbyist web app projects have lost the option of free hosting on Heroku, but that’s no reason to stop learning. Heroku is not irreplaceable, I’m sure I can figure out something if a project proceeds far enough to be worth the effort. So, back to learning: where should I go next? Looking at project ideas that involve Node.js and potentially Express, I decided the next area of focus is a backing datastore. It’s time for some database refresher work starting with Codecademy’s “Learn SQL“.
I’ve taken several database courses in the past, to varying levels of rigor and depth. I expected the introductory material of this course to be review so I’m better able to learn new concepts later in the course. As it turned out, this course was entirely review for me but to be fair, some concepts were fresher in my mind than others. I especially appreciated the cool animations illustrating various table joins.
Another reason to take a SQL refresher course now is to review all concepts from a new perspective. Now that I am thinking of using a database as backend storage for a web application. From this perspective, some of SQL features make less sense than in other contexts. For example, I’m not sure ORDER BY makes sense to do within the database engine, as a web app almost certainly needs to have sorting logic anyway. Think of the shopping sites that lets the user reorder by availability, by lowest price, etc. For small datasets I’d want to do that on the client end instead of round-tripping each new sort as a new query all the way to the database. But the story changes for large datasets. It’ll make sense to sort data on the database if we want things ordered and then LIMIT to the top X items. That reduces bandwidth consumption between server and client and would be a good tool to have.
In contrast, other features like CASE (to categorize values), AS (to rename columns), and ROUND (rounding numbers) are definitely tasks better performed on the client end. I can’t think of a scenario (yet) where it makes sense to do that work on the server-side database.
This course touches on the concepts of primary keys and foreign keys, but other than uniqueness we didn’t get any further details of relational database design. This course didn’t cover concerns of properly designing a database to suit the task, such as database normalization. As a result, this course is good for setting someone up to use an existing database, but not enough to help them set up a new database. Or at least, not an efficient or effective one. Maybe that’ll be part of another course.