This post is going to be broad and fragmented, so forgive me. I have been using rails to create databases with active record for my projects which is basically a builtin feature in Rails that helps with your database table creation. It allows you to use ruby for your database and rails conventions takes care of the rest. But there are still many times you will need to know SQL in more specific senarios such as redoing old sites into rails and perhaps to maximize performance N+1 query problems.
When building a SQL database you it helps to obviously use a diagram whether by hand or with some program like mysql workbench. This helps you set the tables visually, make the associations between tables, and get a broad look at what you are creating. If you follow the programs conventions it will even write the SQL statements for you.
I know there are many books out there but I when ask more experienced developers what I should read to better my understanding of actually creating a working database for some project I get blank stares. It looks like the process isn’t well documented it is just something you have to learn by experience. The fundamentals and syntax to guide you, the rest are a few tips here and there, but mostly experience. Which frustrates the heck out of me, because that takes time! Anyway here are a few tips from blogs I’ve read, maybe it will help you guys!
– what is the nature of your application, Transactional or Analytical?
Transactional meaning more of a CRUD app, such as a blog, or store application with buying and selling. Where the end user will be creating, updating, deleting records. Analytical applications are more for reporting, analyzing. They typically have less inserts, and updates. For this database you want to get and analyze data with priority being speed for those actions. This will affect your decision to go for a more normalized table structure or not. Analytical databases can benefit form having more de-normalized structure. Of course situation dictates.
I’ll put up more tips on future posts.