books/blogs on database design?

[urlwolf]

You know, you design your database, and once your app has been out for a while, you realize how poor some design choices were... and how painful re-engenieering the db will be.

I'm trying to minimize all this. It looks like database design is a black art (as is designing an OO representation of a domain).

Do you have any good pointers for getting proficient at this in a short time? All books I could find are about all the other technical aspects of DBMS...

Thanks

[ashwken]

Just wondering, have you tried a Google search on "database design" or "database design sql" or which ever db you're using?

[iphigenie]

It depends what your level is...

Paul Dubois' Mysql book is one of the best written introductory technical books I have ever read, although it only covers some of the basics on database design. It has great tutorials in perl, php and python (iirc) and will cover things like indexes, stored procedures etc. beyond pure database design and into practical performance. But it does not go in depth. It is a good book even if you dont use mysql, really, because it is concise, well written - it can help someone get started in database-backed web development, picking up perl or php etc. You are probably beyond it, but i thought I would pimp it, I always made trainees read the perl or php chapter there when they had to pick up the language.

Joe Celko's books, starting with "SQL for smarties", are some great books to learn how to do very sophisticated things with SQL (I have the data mining and the binary tree books and there are some great tools in there, although he tends to be a bit single minded about them). They are not about performance and intelligent design, they are more about things you thought you needed to do in code but actually can let the database do...

After that it is about picking a book about the particular database you have chosen, since there are a lot of quirks. If you use postgres I can recommend a few but for others I cant help much

[tinjaw]

The only thing I can contribute to this thread is that Refactoring Databases: Evolutionary Database Design by Scott W. Ambler and Pramod J. Sadalage is on my ToBuy/ToRead list. I own some of Ambler's other books and read lots of his articles and think he does a good job.

[urlwolf]

Thanks all.

I have dubois' book, for example.

Well, the skill I'm thinking is clearly database-independent. It's not about all the nifty tricks you can do to improve performance (which may well be db-dependent) but how to design the tables, how they are connected. This is why I think it's similar to designing a hierarchy for objects.

Imagine that you need to add a a feature to a system that is in production already. If the system is well designed, it's a simple matter; maybe add a column to a table. If the system was designed as an afterthought, you may find that you need to alter the entire db schema!

Some problems I can imagine: adding micropayments to this forum. Was it easy? I don't know. But it probably depends on how the database was designed.

That is the kind of book/blog I want to find.

This is kind of a black art.

For those with formal CS backgrounds... Is there any subjects in the standard CS curriculum that cover that? Say database design 101? Or maybe relational systems? Or object-oriented design?

Thanks

[rjbull]

I heard - from an article in the computing section of the paper, I think - that the following is "the one." The details are those from our county library record.

Author Date, C. J., Christopher John 1941-
Title Database systems
Publisher Addison-Wesley
Price £27.99
Summary Covers the whole spectrum of opportunity from design to retail, from fashion journalism to modelling. Fully updated 5th edition.
Contents (Each Chapter begins with an Introduction and concludes with a Summary, and each Appendix begins with an Introduction): I. PRELIMINARIES 1. An Overview of Database Management 2. Database System Architecture 3. An Introduction to Relational Databases 4. An Introduction to SQL II. THE RELATIONAL MODEL 5. Domains, Relations, and Base Relvars 6. Relational Algebra 7. Relational Calculus 8. Integrity 9. Views III. DATABASE DESIGN 10. Functional Dependencies 11. Further Normalization I: 1NF, 2NF, 3NF, BCNF 12. Further Normalization II: Higher Normal Forms 13. Semantic Modeling IV. TRANSACTION MANAGEMENT 14. Recovery 15. Concurrency V. FURTHER TOPICS 16. Security 17. Optimization 18. Missing Information 19. Type Inheritance 20. Distributed Databases 21. Decision Support 22. Temporal Databases 23. Logic-Based Databases VI. OBJECT AND OBJECT/RELATIONAL DATABASES 24. Object Databases 25. Object/Relational Databases Appendix A: SQL Expressions Appendix B: An Overview of SQL3 Appendix C: Abbreviations, Acronyms, and Symbols
Description For over 25 years, C. J. Dates An Introduction to Database Systems has been the authoritative resource for readers interested in gaining insight into and understanding of the principles of database systems. This exciting revision continues to provide a solid grounding in the foundations of database technology and to provide some ideas as to how the field is likely to develop in the future. The material is organized into six major parts. Part I provides a broad introduction to the concepts of database systems in general and relational systems in particular. Part II consists of a careful description of the relational model, which is the theoretical foundation for the database field as a whole. Part III discusses the general theory of database design. Part IV is concerned with transaction management. Part V shows how relational concepts are relevant to a variety of further aspects of database technology-security, distributed databases, temporal data, decision support, and so on. Finally, Part VI describes the impact of object technology on database systems.

Amazon UK link:  http://www.amazon.co.uk/exec/obidos/ASIN/0201684195/virtuallibrar-21

[urlwolf]

Thanks rjbull, that definitely sounds like a good book.

[Lashiec]

That book is a classic, and the recommended one of the whole bibliography we use at Database Design in my college.

[iphigenie]

It really depends on your level and your aim - there are very different aims in an academic textbook and a learning book for the pragmatic practitioner

The best is to indeed take the time to learn the theory, then learn the ins and outs and quirks of real world practice - which imply to throw out the window half the theory because real databases dont work that way, there are performance concerns and simply if you want to write something that is easy to write and maintain

I still recommend "SQL for Smarties" or "Database in Depth" which is by the same C Date as the textbook above but oriented at practitioners rather than at a 2 semester course for undergraduates

[patthecat]

The topic you should be searching for is the term "data warehousing" or "data modelling".  Data warehousing and data modelling is all about getting the proper database design (cases when you should "normalize" or not, when to split data into different relational table, etc) as opposed to the technical aspects and implementations of a specific RDBMS.

Search amazon for "data warehouse" and you'll see books by Ralph Kimball - the "father of data warehousing".  I like his books since it really focuses on "real world" data modelling cases.  Other database books either give too much overview/theory or too specific in the technical operations of a specific database system.

patrick

[Renegade]

I love working with databases. It's just fun!

I can't recommend any better books, but I would probably say that it helps to read one on the database that you're planning to use. I generally use MS SQL Server, MySQL (if I absolutely have to and have no choice), and VistaDB.

MySQL has a lot of things that just irk me. VistaDB I only use for desktop applications. MS SQL Server I like for everything else. There are a lot of things in T-SQL that expand on SQL that are good to take advantage of if you know that is what you're going to use. Similarly, other databases have their own "tweaks".

Time to spin off another thread -- got a related thing I'd like to chat about...

[Renegade]

Ok... So... (Trying not to hijack this thread here -- please post in the thread below.)

How Do You Like to Approach Database Design?