ORDBMS and Reusable Design: Why PostgreSQL on Your (Next) Project

One of the classic songs during the British invasion of America through music is "I'm Henry the Eighth, I Am" by the Herman's Hermits. The main character of the song is a man named Henry about to marry a widower who married seven times before. All the groom she married were named Henry. Probably the lady in the song found some particular trait(s) found in every Henry she met.

I hope you'd allow my fancy for the moment and share the perspective when we take the case of Object-oriented Programming (OOP) and Object Relational Database Management Systems (ORDBMS), given that, like Henry the Eighth in relation to his bride's previous husbands, they have the same first name. Would it be possible that design and/or code reuse, one of the great tenets of Object-oriented programming be applicable to Object relational database systems? Am I dragging your leg? Read on and find out.

In a previous project, I designed an e-commerce database back-end as illustrated below:

Most of the tables' purpose being obvious, the entry_type table determines if the item is for classified, auction, barter or looking for section of the e-commerce site. Note that I used entity relationship notation

For background on the schema design, the Items table holds the entry for the e-commerce site’s for sale, for auction, for exchange, and looking for item entries. If you closely look at the database schema diagram above, you would observe that there are table fields that are either redundant or irrelevant for certain table entries, making the design inflexible while also wasting storage space, and to top it off, I built it on an object relational database management system, specifically PostgreSQL.

Admittedly, I made a big mistake of not maximizing PostgreSQL, making that design the poster child of my design immaturity at the time that project was designed and implemented. Beyond the sleepless nights of guilt this may or should have ensued depending on how you prefer to look at it, we must learn from our mistakes and apply the lesson learned for future endeavors. Knowing what I know now, the design should have been like so:

Note that I used object-oriented programming notation in this diagram

You may think I'm playing tricks on you knowing that in the first diagram I used entity relationship notation, while on the supposedly improved design, I used object-oriented notation.

Basically, given the object-relational capability of the likes of PostgreSQL (in the league with Oracle and MS SQL Server on the Object-Relational DBMS space), you can think of the database schema design in object-oriented mindset. Referring to the diagram above, the e-commerce database back-end is now more simplified as far as maintenance and coding is concerned (for marketing speak, we can throw in there the "separation of concern" buzzword).

A General Purpose Schema Design
If object-oriented programming languages have code reuse as one of the virtues of the code and systems management, would it be feasible to design a schema that can be reused for various projects or problem domains?

Thinking hard on this matter for so long (a partial reason or excuse if you will for my long blog hiatus) that my idle time were moments well spent, since I formulated some ideas that I hereby present below for your perusal and hopefully, you'd get to learn something worthwhile.

The Base Schema Design Using Object-Relational DBMS Design
What does an automation system for education, health and hospitality management system in common? Basically, they are all resource allocators, allotting the use of resources for use by particular actors (i.e. users or consumers). But more deeply than that, we can simplify this relationship and extract the key components of the systems: they are actor and space.

We will shed light how actor and space would fit our discussion later in this article

The Scholar, The Healer and The Accommodating Host 
Using actor and space as the base of the database schema architecture, we can design school management systems as follow:

Note that from the actor object, we derive student and school staff, upon which we derive the instructor and admin. In the case of space, we can derive school room, like classroom and lab, and lastly, from space we can plan for parking.

The school staff can also have provision for maintenance crew, canteen staff, school supplies and other kind of roles within the school context. In the case of space, we can also set provision for library, canteen, auditorium or activity area.

Practically, with the design I propose in this article, the school management system is flexible to prepare for unforeseen future expansions of the setup, like new roles or new resources for use (i.e. clinic).
 
Likewise, in health-care management systems, we can define the actors and their corresponding space used as resource like so:

Note the derivation of client, physician and nurse from the actor entity? Accordingly, ward and lab inherits from the space entity.

We can further expound and elaborate the idea of this design, maybe also have parking space considered in the design; or have an admin entity to represent operations management of the facility.
 
The same principle can apply to hospitality management systems, as demonstrated here:

This isn't the most comprehensive nor the best design we can come up with as far as hotel and hospitality management systems go, but should give you an idea

As can be seen from the examples, with the Object-relational DBMS like PostgreSQL, design and even SQL code re-usability is feasible and will be an advantage in the design, implementation, maintenance and administration standpoint of any database-driven project.
 
You might argue that the entity definition is too definite and could be limited, but that same limitation is the strength; we're more certain that we refer to specific entities, and minimize if not totally avoid discrepancies. At the same time, ensuring we can reuse the same architecture or design, and maybe even the code, this time for database architecture design and programming.


I hope I have convinced you enough to consider ORDBMS in general and PostgreSQL in particular as the database back-end for your next project.