↓ Ir para o conteúdo principal

← todas as notas

📎 Webclip

SOLID Database Design With PostgreSQL

Chris Travers’s series on “Building SOLID Databases” applies several OOP principles to data models and database code. The article focuses on PostgreSQL features such as table inheritance and shows how they can support extension points, shared structure, and database interfaces.

Reading notes
#

  • Normalization is usually enough to satisfy single responsibility in a purely relational database, but table inheritance can help when fields commonly appear together and depend on other fields.
  • For notes, a purely relational design may use many separate notes tables or one global notes table with join tables, while an object-relational design can use multiple notes tables inheriting from a common notes table.
  • Open/Closed can be supported with table inheritance, as in pg_message_queue 0.2, where separate tables handle different data types while inheriting from one common table.
  • Liskov Substitution can break under table inheritance, as shown by a square inheriting from a rectangle and failing when an update on the parent changes height without preserving the square constraint.
  • One way to handle that problem is to avoid updates and keep rows immutable; another is to use triggers that move rows between tables when updates occur.
  • Interface Segregation in databases mainly concerns user-defined functions and stored procedures, which Chris treats as interfaces to the underlying data.
  • He suggests that a function or stored procedure should usually contain one large query with minimal surrounding logic, and that many optional parameters or more than five queries may indicate unnecessary complexity.
  • Dependency Inversion can reduce leaky abstractions when application logic is tightly bound to stored procedures, and possible tools include a service locator pattern, views, functions, custom datatypes, triggers, and notifications.
  • The main design advice is to treat the database as an application that exposes an appropriate API.