Many Real Studio projects require the use of a database. In 10 years of Real Studio consulting and many, many projects both internally and for clients, I can tell you the projects that did NOT use a database simply because they are the oddities in my career. If truth be told, even some that didn’t use a database could have been, and perhaps should have been using a database.
A lot of Real Studio developers avoid them because the term ‘database’ is scary and mysterious and brings up images of having to wrestle with huge problematic installations of MS SQL Server, PostgreSQL and MySQL. While you might have to use them eventually, we do a lot of development on the lowly and surprisingly powerful SQLite. It is a lightweight database that offers most of the features of the big database servers. But for many, SQL, the language behind databases, is mysterious and arcane. Not so!
At last weeks Real Studio Database Days training in Frankfurt, Simon Larkin of QiSQL gave a very interesting talk about SQL. He also has a section on his website devoted to learning SQL. His SQL School takes you through the basics of the terms and then starts introducing you to more and more complex situations.
Equally important is his Database Design section that walks you through the best ways of designing your database. Learn about primary keys, entities, relationships and database normalization. All in all the tutorials are very well done and if you’re just starting to use databases this is a good place to start.
Simon likes to use raw SQL, which is fine, but I’m lazy and SQL-challenged at times so I tend to use a number of SQLite utility applications. Since each seems to have some compelling feature over the others I use the one that best fits the project. I use Base, SQLiteManager, NaviCat, and the FireFox plugin SQLite Manager. Of these, SQLiteManager and NaviCat can open encrypted databases and only Navicat being able to remember the encryption key. Navicat also has a visual Query Builder that newbies to SQL might find attractive.
What SQLite tool are you using and why?