Book Review: Database Design and Modeling with PostgreSQL and MySQL

Book Review: Database Design and Modeling with PostgreSQL and MySQL

I’ve had a busy time reading books lately. Another one from my friends at PackT that looked interesting to me was Database Design and Modeling with PostgreSQL and MySQL: Build efficient and scalable databases for modern applications using open source databases by Alkin Tezuysal and Ibrar Ahmed.

PostgreSQL and MySQL

Database design and modeling is a passion of mine and, while most of my time in recent years has been spent on the SQL Server side of the fence, I’m always involved in PostgreSQL projects, and occasionally with MySQL. Each database has “a way of doing things” that’s considered best practices. MySQL has never been a database engine that I’m keen on, but with tools like WordPress, it’s always been part of some projects.

PostgreSQL on the other hand, continues to show real promise and I love it’s focus towards what developers really need. Some time back, I started the PG Down Under podcast and I expect to develop that much further later this year.

PG Down Under Logo

The “style” in most of this book tends to feel more related to MySQL than PostgreSQL.

Content - Overall

The book basically discusses what the different types of database engines are (SQL, NoSQL, etc.), then covers the basic attributes of relational databases.

Next, it shows how to design a database, by covering normalization, ER diagrams, etc. And then shows how to use the tools to do this.

Two aspects of this are worth mentioning:

  • First, I don’t see SQL, NoSQL, etc. as the main differences now. Most NoSQL database engines offer SQL interfaces. (They usually now call themselves “not only SQL” instead of “no SQL”). By “SQL”, it’s really talking about “relational” in this book.
  • Second, there is an implicit assumption in the book that you are designing an OLTP (transactional style) database. That’s not always the case. You might be using a relational database engine to build a data warehouse, and if so, many of the rules are different.

Content - Editing

The overall English writing style in this book is good. It is very readable.

I have a friend that recently mentioned to me that the scariest thing when he first opens a copy of one of his new books, is that he immediately sees typos that were missed. I noticed that in this book. For example “tale” appears where “table” is meant.

And there are even typos in the column names in the sample code like product_description_lenght. (clearly meant to be “length” not “lenght”). Similar issue with “product_name_lenght”, etc.

Another aspect of copy editing, is that there are some blocks of really dense text, that should be broken into paragraphs. Here is an example:

Really dense text paragraph

This can make reading harder than it needs to be. Overall, some further copy editing would help.

Content - Samples

I like the fact that you can download a PDF of the book if you have purchased the physical book. I also really liked the way that sample code is provided via a GitHub repo. This is a really good option that I wish others would follow.

As the focus of the book is on design, I have to be a bit picky about what I see in the designs:

  • One thing that I didn’t love is the inconsistency and errors in the sample data model. For example, there is a mixture of singular and plural table names, even for things that are used similarly.
  • Another note is that columns like “delivery_date” should really be “date” data type rather than “datetime” when using MySQL, as it does have a “date” data type. I have a preference that columns with a name ending in “date” just contain dates.
  • I’m not a fan of preceding every column’s name with the name of the table.
  • I see no rationale for having a column named StudentNAME rather than StudentName.

Content - General

The book starts by covering the different types of database engine, then describing normalization, and how to design a (transactional) database using ER diagrams to get thoughts together, and then using the actual database tools.

It was good to see a discussion on data types and their usage. One aspect missing was a discussion on “float” vs “decimal” as that’s something newbies regularly get wrong.

The discussion on constraints and related code is fine. I would have liked to see more on uniqueness in this section. It’s an area that’s often messed up in database designs.

The discussion on CTEs seems more appropriate for earlier versions of PostgreSQL. Nowadays, there is subtlety around materialized CTEs vs non-materialized CTEs. This should be explained. Also, as CTEs are returning sets of rows, their names should reflect that.

I would like to have seen security emphasized far more, and in its own section. Too many people try to “sprinkle security on later” when designing databases, and it’s never a good idea.

Summary

Overall, it’s an interesting read. Having the book cover both feels odd to me. I think I’d rather see two versions of the book, each dedicated to a single database engine. But it covers a worthwhile range of topics.

6 out of 10

2024-10-02