SOLID Database Design with PostgreSQL

Chris Travers recently published a series of articles called “Building SOLID Databases”. It explains several ideas on how to apply some of the common OOP principles such as single responsibility principle, interface separation, dependency reversal to improve data models and database code. While some of the ideas can be applied in part to any relational database, the articles present scenarios involving object-relational features such as table inheritance, available in databases like PostgreSQL.

In Sole responsibility and standardization, Chris explains the subtle similarities and differences between data models and class models. Normalization is normally sufficient to meet SRP in pure relational databases, but table inheritance can be used additionally to handle co-occurring fields that depend on other fields in the database. He gives an example –

A common case where composition makes a big difference is note management. People may want to attach notes to all kinds of other data in a database, and therefore the text or subject of a note cannot be said to be dependent on each other.

A typical purely relational approach is to either have many independently managed grade tables, or have a single global grade table that stores grades for everything, and then have multiple join tables to add join dependencies.

An object-relational approach can be to have multiple note tables, but have them inherit the table structure of a common note table.

In the Open / closed principle, the goal would be to keep the system expandable, without causing extensions to break when the base version changes. Again, table inheritance can provide a flexible way to provide extension points for data models – the example here shows how pg_message_queue 0.2 can handle different data types by having a separate table to support each data type, all inheriting from a common table. Chris also provides another simple example where a secure API is kept extensible for security checks but closed for changes.

the Liskov substitution principle is normally not a problem for purely relational databases, but could come to the fore when using table inheritance. An example here is a my_square table inheriting from a my_rectangle table –

CREATE TABLE my_rectangle ( id serial primary key, height  numeric, width numeric );
CREATE TABLE my_square ( check (height = width) ) INHERITS  (my_rectangle);

and run an update on my_rectangle –

UPDATE my_rectangle SET height = height * 2

then it will cause repository problems in the square array and fail. Ways to deal with this would either be to either avoid updates altogether (keep rows immutable) or use triggers to remove rows from my_square and insert them into my_rectangle whenever such updates are performed.

Interface segregation when applied to databases would primarily involve user-defined functions or stored procedures. Chris sees them as interfaces to the underlying data and suggests that the ideal function or stored procedure would have a large query with minimal surrounding logic – more than 5 queries or a large number of optional parameters might indicate reducible complexity which should be processed by breaking down into several separate functions or stored procedures, each for a specific purpose. This still goes hand in hand with the principle of single responsibility.

In Robust database interfaces and dependency reversal, Chris explains how the tight connection between application logic and stored procedures can lead to leaky abstractions and suggests a few potential solutions. Some of them use something that looks like a service locator template, using views or functions, using custom data types, triggers and notifications. Their key suggestion here is to look at various options and design the database itself as an application exposing an appropriate API.

Maria H. Underwood