How can I improve my database design and query skill?

Question

I have been doing web development using python for a year.

And all the projects I took part in are small-scale intranet applications, which didn't require much design skill and performance of queries just didn't matter in most cases.

That means I can use an ORM like django.db and SQLAlchemy and forget about the database details.

But recently I found that I was a lot less skillful when writing raw sql queries than I once was.

Have you ever been in a similar condition?

How do you improve your database design and query skill?

Answer 1

Where do we start?!

Read about how to design a database using Entity relationship diagrams (ERDs). This will get your head in the right place.

Read about database normalisation and aim to have any database you design in third normal form (3NF).

SQL is all about practice! Format it in such a way that it's super readable and easy to follow. Avoid using Microsoft designers to write SQL, they suck. Avoid right outer joins in queries, instead work with inner joins and left joins. You will find this makes your queries easier to wrap your head around.

Answer 2

There were many things I disliked about working in financial services, but I did get alot of experience writing and optimizing SQL.

I frequently had to write ad-hoc SQL Server/Oracle queries/sps for reports and optimize them for overnight runs. The databases in financial applications are complex and huge, in terms of tables and rows and sometimes there was time pressure, not to mention people literally standing behind me, breathing down my neck for results ASAP.

I'd find some big sample databases and try to simulate the same thing. You'd learn even more if you build your own database backed app and forgo the ORM or have the ORM call sps you write yourself. I wrote my own shopping cart web app and the database for that can get involved. You might be re-inventing the wheel, but you'll learn alot.

There are plenty of mySQL blogs, you can read Celko, there are SQL brain teasers in SQL Magazine (I think you have to pay though.) Obviously, there are plenty of SQL brain teasers here in Stack Overflow.

Answer 3

Pick a database to learn

First understand joins - thoroughly. Do nothing else until you absolutlely understand joins. To do anything with a relational database without a thorough understanding of joins is like trying to learn to read without learning the alphabet.

Learn how to use a where clause

Read about indexing.

Learn about aggregate function and grouping data.

Now is the time to learn normalization (it will make much more sense if you understand how to query first).

Buy a big fat book on performance tuning for the database backend of your choice and read it cover to cover. You will find plenty of best practices and things to never do. You will also find that you currently do alot of the things in the never do list.

Get another big fat book on refactoring databases. Databases live much much longer than applications and are much harder to refactor if you don't think about how to do this from the start. Here's a good book to read cover to cover: http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr_1_1?ie=UTF8&s=books&qid=1268158669&sr=8-1

Databases need to be designed from the start for data integrity (never rely on the application for this, it is one of the top 10 stupid mistakes you can make in database design), performance (this is not premature, there are known performance killers to be avoided from the start that are much harder to refactor later) and security. They also need to be designed to be able to capture audit records and to do refactoring.

Have fun. If you get that far, then consider learning about data warehousing, reporting and ETL processes.

Answer 4

Pair up with someone more experienced! Read tutorials with comments!

Answer 5

See if you can pick up a used database textbook on the cheap. They usually have a nice blend of theory and practice. The book can help you familiarize yourself with the normal forms, transaction management, etc. It may not have any hard benefits, but this helps you brush up on your soft skills, which really come into play when conceptualizing new databases, or writing a complicated query.

This is a decent book, it has some nice screenshots and diagrams that give some rhyme to reason: link text