Can you recommend me books that you like the most in database design and optimization?
Edit: General design and optimization books (database-independent, no matter it's Oracle, SQL Server or DB2,...) are great. But database-specific books are still great. I would appreciate any ones. :)
The best platform agnostic book I have found is Database Design for Mere Mortals by Michael J. Hernandez
Previous related answers:
Book recommendation for advanced SQL design, scripting and optimiziation.
What are some resources I can use to learn profiling/optimizing?
Platform specific:
J. Patrick Thompson, Data With Semantics: Data Models and Data Management.
Note: this is a book about databases, but not RDBMSes or SQL. In addition to the normal scholarly writing, it employs Socratic dialogs among various characters reprsenting archetypal ways of thinking to teach the reader to think about data modelling (reminiscent of Galileo's Dialogue Concerning the Two Chief World Systems).
Its main thrust is that designing a database means modeling a domain, and that means asking "what is truth", or "what aspects of the truth do I need to model, and what are extraneous to my solution".
Again: no RDBMS, no SQL. Very abstract. Out of print. Twenty years old. Well worth reading.
Comment:
I generally find that "out of print" and "well worth reading" are kind of at odds with each other. If it really is worth it , it would not go out of print , no? – Learning
Or perhaps it was ahead of its time. The book documented a design for a "Semantic Database" with Object Oriented properties. Today, we talk about a "Semantic Web and heavily use ORMs that translate our OO entities to and from RDBMS relations. Or maybe time and forgetfulness have given it an undeserved patina in my memory.
But I do remember that reading it was one of those "ah! aha! I get it now! you can do that? oh man, that's so neat!" experiences, the same feeling I later had reading Coplien's Advanced C++ Programming Styles and Idioms or Alexandrescu's Modern C++ Design.
SQL Tuning is a great book to help understand how SQL queries are run under the covers. It provides a general approach that can be used to "help" a db's query optimizer construct best execution plans for specific complex queries you want to speed up. The advice applies generally but the book also clarifies differences between major databases. The book's a little bit old (2003?), so it's possible some of the differences between specific databases might have changed, but the general tuning principles it outlines still apply. Excellent book.
Data Modelling by G. Lawrence Sanders is an essential book on database design. It's very short and written in plain English. It somehow manages to get to the core issues of database design from a conceptual standpoint in a very straightforward way.
You won't find any information about optimization or technical issues here, but you will get some good insight into how to become a better designer.
Note: ignore the section on TQM near the start of the book (consider it a Persian flaw).
When it comes to Oracle I advice the books of Tom Kyte. When you want to optimize your database you need to pay attention to the vendor specific features and behaviour. For instance it is very important to understand the Oracle's library cache when optimizing Oracle.
I recommend that you should read the book Beginning database design. It's a great db design book.