Help me become good at relational databases
October 28, 2015 10:04 AM Subscribe
I'm a fairly seasoned developer but not much of a relational database expert, though I know the fundamentals. I'm looking for resources to get to the next stage.
Throughout my career, I've either used NoSQL databases, used an ORM to abstract a relational database away for simple use cases, had someone else do the database work, or did the basic work myself - with a lot of groaning.
I'm proficient enough to know how to design a normalized database structure (which I think is 3NF but can't be bothered to verify that), how to write decently complex queries with subqueries and joins and such, but that's it. And I've never really enjoyed the work.
Now, I have to maintain and improve a fairly structurally-sophisticated and heavy-use relational database. So I need to take my skills to the next level. I'd like to get good at writing complex queries quickly. I'd like to develop a deep understanding of indexes, where they're needed, and what performance trade-offs they involve. I need to develop an intuition for all the different transaction types and when to use which. Generally, I need to understand concurrency and locking. And I'm sure there's many other intermediate-to-advanced topics that aren't even on my radar.
Note: I want to learn just enough of the internals of relational databases to achieve my goals, NOT to learn how to build one. I'll mostly be working with Sql Server and PostgresSQL.
Any suggestions for resources - books, online courses, video series, problem sets, whatever, would be appreciated.
Throughout my career, I've either used NoSQL databases, used an ORM to abstract a relational database away for simple use cases, had someone else do the database work, or did the basic work myself - with a lot of groaning.
I'm proficient enough to know how to design a normalized database structure (which I think is 3NF but can't be bothered to verify that), how to write decently complex queries with subqueries and joins and such, but that's it. And I've never really enjoyed the work.
Now, I have to maintain and improve a fairly structurally-sophisticated and heavy-use relational database. So I need to take my skills to the next level. I'd like to get good at writing complex queries quickly. I'd like to develop a deep understanding of indexes, where they're needed, and what performance trade-offs they involve. I need to develop an intuition for all the different transaction types and when to use which. Generally, I need to understand concurrency and locking. And I'm sure there's many other intermediate-to-advanced topics that aren't even on my radar.
Note: I want to learn just enough of the internals of relational databases to achieve my goals, NOT to learn how to build one. I'll mostly be working with Sql Server and PostgresSQL.
Any suggestions for resources - books, online courses, video series, problem sets, whatever, would be appreciated.
I got started with two books from the "for Mere Mortals" series a long time ago and they were great: SQL Queries and Database Design.
posted by yerfatma at 10:54 AM on October 28, 2015 [1 favorite]
posted by yerfatma at 10:54 AM on October 28, 2015 [1 favorite]
SQL Server Central has a lot of useful resources.
posted by terretu at 11:29 AM on October 28, 2015 [1 favorite]
posted by terretu at 11:29 AM on October 28, 2015 [1 favorite]
SQL Antipatterns is a pretty good book. Not sure if this is the level you're looking for.
posted by pyro979 at 7:28 PM on October 28, 2015
posted by pyro979 at 7:28 PM on October 28, 2015
Simpler than a book, just learning to use your DBMS's EXPLAIN command can help your understanding of how indexes will (or won't) help with a given query and how to write more efficient queries.
Obviously it isn't terribly useful for theory, but it can help eliminate boneheaded mistakes between now and whenever you are fully up to speed. If your DBMS has good documentation (like Postgres does), that can also help you figure out what it is you don't know and will need to know and give useful search terms for those things.
Books aren't bad, but most of them are too much about practice and too little about theory for my taste. The web has lots on the latter and much less on the former. Generally speaking, I have always been happy with the O'Reilly books on whatever subject. They tend to have a good mix.
posted by wierdo at 10:57 PM on October 28, 2015
Obviously it isn't terribly useful for theory, but it can help eliminate boneheaded mistakes between now and whenever you are fully up to speed. If your DBMS has good documentation (like Postgres does), that can also help you figure out what it is you don't know and will need to know and give useful search terms for those things.
Books aren't bad, but most of them are too much about practice and too little about theory for my taste. The web has lots on the latter and much less on the former. Generally speaking, I have always been happy with the O'Reilly books on whatever subject. They tend to have a good mix.
posted by wierdo at 10:57 PM on October 28, 2015
Best answer: It sounds like you're already pretty well versed in the basics. I've found that Inside Microsoft SQL Server 2008 T-SQL Querying is an excellent extension for understanding indexes and optimization. (I refer to the 2008 version because that's what we use in my office.)
Use The Index, Luke! is handy as well.
posted by mikurski at 11:17 AM on October 29, 2015
Use The Index, Luke! is handy as well.
posted by mikurski at 11:17 AM on October 29, 2015
Response by poster: Thanks for the hints all, the first two were particularly golden.
Use The Index, Luke! is handy as well.
This site is actually an excerpt from the book mentioned by andrewcooke upthread. Which I bought and so far is just at the right level for me, although it ONLY covers query performance and indexing, and nothing else.
posted by tempythethird at 4:11 PM on October 29, 2015
Use The Index, Luke! is handy as well.
This site is actually an excerpt from the book mentioned by andrewcooke upthread. Which I bought and so far is just at the right level for me, although it ONLY covers query performance and indexing, and nothing else.
posted by tempythethird at 4:11 PM on October 29, 2015
This thread is closed to new comments.
posted by andrewcooke at 10:42 AM on October 28, 2015 [3 favorites]