Archives For
database

I generally don’t like to link to things that require that you sign in to access the content, but I thought this question and answer on Quora was worth sharing. The question is: “Why is it hard to scale a database, in layman’s terms?” To which Paul King put forth a useful and easy to understand answer.

Whenever you begin working with a database, you introduce more possible causes of errors. Thus, you must learn additional debugging strategies. When using PHP to run queries on the database, the problems you might encounter include:

An inability to connect to the database
A database error thrown because of a query
The query not returning the results or having the effect that you expect
None of the above, and yet, the output is still incorrect
On a non-framework site, you just need to watch for database errors to catch the first two types of problems. There’s a simple and standard approach for debugging the last two types:

Use PHP to print out the query being run.
Run the same query using another interface to confirm the results.
Debug the query until you get the results you want.
When using a framework, these same debugging techniques are a little less obvious, in part because you may not be directly touching the underlying SQL commands. Thankfully, Yii will still be quite helpful, if you know what switches to flip.

The CDbCriteria class let’s you customize queries through an object. The first thing you should do to become more comfortable with Active Record when using the Yii framework is master usage of CDbCriteria.

This is an excerpt from Chapter 8, “Working with Databases,” of “The Yii Book”.

Some time ago I stumbled upon the WWW SQL Designer, an online database modeling tool, created by Ondrej Zara. It’s built in JavaScript, and runs in the browser, although PHP is required for some functionality. You can download the code from its Google Code home page so that you can run it in from your own computer, but to see a demo of it in action (a completely usable demo), head to http://ondras.zarovi.cz/sql/demo/.

There’s not much in the way of documentation, but the tool is pretty straightforward, and there’s a YouTube video, if you want. To start, just create a new table, giving it a name. The tool will automatically create the autoincremented primary key for each new table. Then you can add other columns and indexes (aka keys). Add more tables as needed, and you can easily indicate the relationships between tables by marking foreign keys. Once you’re done with the design, you can export the SQL. The tool supports many different databases, including MySQL, SQLite, Oracle, and PostgreSQL. The outputted SQL is also nicely broken down, separating, for example, the foreign key statements and table properties (e.g., the character set, collation, and storage engine) into standalone ALTER queries, allowing you to use whatever commands you need when you actually go to create the database in your database application. The generated SQL even provides demo INSERT statements for the tables. Alternatively, you can save the design as XML, allowing you to reload that design in the application for further editing at another time.

I’m always on the lookout for a good database design tool, so was pleased to find this one. It’s particularly useful when I’m not on my primary computer, such as when I’m at a client’s office, and need to whip up some SQL on the fly. Check it out for yourself and see what you think. The price—free—is right!

phpmaster recently posted an article titled Introduction to MongoDB. I’m not entirely sold on non-relational databases yet, in that while I can see how wonderfully beneficial they can be in many situations, they’re not as ubiquitously useful as all the hype would seem to suggest. But in any case, a good article like this one, which introduces MongoDB, shows how to install support for it in PHP (although the instructions are for Unix-like systems), and provides code for actually interacting with a MongoDB database is worth the time to read.

About Larry Ullman

Larry Ullman is a writer, developer, teacher, speaker, and consultant. He has written more than 20 books and numerous articles. His books have sold over 400,000 copies world wide in more than 20 languages. As his readers, students, and co-workers can attest, Larry’s strength is in Translating Geek into English: converting the technical and arcane into something comprehensible and useful.

After 14 years of working for himself, Larry joined Stripe in 2013. He is currently a Technical Writer there.