What is your goto RDBMS pattern book? | Hacker News Reader

What is your goto RDBMS pattern book?

What could you recommend someone with brief understanding of RDBMS but is interested in how to properly design tables, in some real world scenarios - i.e. messaging table, append only without indices so it inserts are super fast, and attached triggers to process, or this table with job queue... or some scenarios like these... I know we have kafka / elastic search / redis etc., but I'd still be interested if I want to keep infrastructure as lightweight as possible, what would be the patterns to do it in lets say Postgres.

View on HN

I've never come across a great book of relational models specific to domains.

My favorite general db book is Database Systems: The Complete Book (DS:CB), by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom. This is a good book for learning about relational modeling and how to normalize a model so that your eventual schema has as few dependencies between entities as possible. I taught an introductory class where the required (by the department) textbook was Foundations of Database Systems by Elmasri. It's definitely not my favorite book, but if you skim chapter 3 on entity-relational modeling and then chapter 9 on converting an entity-relational model to a relational model suitable for an RDBMs, that is a useful starting place. When I was teaching the class, I often had to go back to the Garcia-Molina,Ullman, Widom book for simple and clear language around concepts for my lectures.

I'll second the recommendation for Joe Celko's books for general SQL programming.

The problem you'll find is that job queues, message queues, etc are actually edge cases. You can implement them in a DB, sure, but to do so with acceptable performance will require deep knowledge of the storage engine, query planner, locking, and DB specific extensions.

For just one example: https://www.mssqltips.com/sqlservertip/1257/processing-data-...

I can't find the article, but I remember reading that to get acceptable performance in SQL Server Service Broker it (under the hood) actually padded rows to ensure there was only one item per page on disk (or something similarly implementation specific to SQL Server).

I've heard people recommend SQL for Smarties. The Postgres docs are pretty awesome, but you sort of need to know what you're looking for before jumping in. You can also find schemas on Github and study living examples.

Reply on news.ycombinator.com